时间戳

首页 > 解决方案 > 如何在 Knex/Postgres 中将三列（其中 2 始终为空）合并为一个新列？

sql - 如何在 Knex/Postgres 中将三列（其中 2 始终为空）合并为一个新列？

问题描述

所以我的数据库中有一个实体，它链接到三个资产之一。

它目前以直接的方式实现，使用表中的三列（asset1_id、asset2_id、asset3_id），其中两列始终为空，另一列将填充所选资产。

出于搜索和排序目的，我希望能够创建一个额外的列来合并这三个资产列中的数据。

如果可能的话，它应该忽略两个空值，然后使用一个填充资产列的名称字段作为这个新计算列的值。

我正在使用 Knex，我看到有一个 .union 函数，但我不知道如何使用它来创建一个额外的列来计算结果。

任何帮助将不胜感激！

标签： sqlpostgresqlknex.js

解决方案

听起来您因为没有早点规范化数据而感到痛苦。这很好，它只是意味着您可能需要考虑一些“升级”。

或者也许是一对多？

根据您对此答案的评论，您可能希望以这种方式安排事情：

╔════════════════════╗
║ users              ║
╠═══════╦════════════╣
║ id    ║ increments ║
║ name  ║ string     ║
╚═══════╩════════════╝

╔══════════════════════════════════════════════╗
║ jobs                                         ║
╠═════════════╦════════════════════════════════╣
║ id          ║ increments                     ║
║ description ║ string                         ║
║ asset_id    ║ integer (references assets.id) ║
║ user_id     ║ integer (references users.id)  ║
╚═════════════╩════════════════════════════════╝

╔══════════════════════════════════════════════════════════════╗
║ assets                                                       ║
╠════════════════════╦═════════════════════════════════════════╣
║ id                 ║ increments                              ║
║ name               ║ string                                  ║
║ characteristics_id ║ integer (references characteristics.id) ║
╚════════════════════╩═════════════════════════════════════════╝

╔══════════════════════════════╗
║ characteristics              ║
╠═════════════════╦════════════╣
║ id              ║ increments ║
║ description     ║ string     ║
║ hasPulleys      ║ boolean    ║
║ hasLevers       ║ boolean    ║
║ colour          ║ enum       ║
║ configuration   ║ jsonb      ║
╚═════════════════╩════════════╝

当然，如果资产特征集不是太大，您可以将它们全部放在assets表中并处理一些将NULL依赖于资产的事实。一切都交给你了......调查JSON/JSONB列类型可能会有所帮助。

查询

要获得一份工作及其资产，您可以这样查询：

db('jobs')
  .join('assets', 'jobs.asset_id', 'assets.id')
  .join('characteristics', 'assets.characteristics_id', 'characteristics.id')
  .select('jobs.description', 'assets.name', 'characteristics.hasPulleys')

或您需要的任何列。

对于后代：如何获取列名

尽管我猜您可以在列中添加列名，但这有点“代码味道”......让您认为可能有更好的解决方案来解决您想要完成的任务。我们需要更多地了解您当前的架构和要解决的问题。

如果您真的非常确定这是您想要的，那么您可以通过以下方法获取值不为 null 的列的名称：

db.with(
  "col_names",
  db.unionAll([
    db
      .select(db.raw("id, 'asset_1' as col_name"))
      .from("users")
      .whereNotNull("asset_1"),
    db
      .select(db.raw("id, 'asset_2' as col_name"))
      .from("users")
      .whereNotNull("asset_2"),
    db
      .select(db.raw("id, 'asset_3' as col_name"))
      .from("users")
      .whereNotNull("asset_3")
  ])
)
  .select("col_name")
  .from("col_names")
  .where("id", userId)

或您要搜索的任何 id。应该强调的是，这不是一个灵活的或面向未来的解决方案。

推荐阅读