如何在Knex查询中根据数据库条件选择性返回字段？

我用Knex创建了两张关联表users和users_info，表结构如下：

users表创建代码 #

exports.up = function(knex) {
  return knex.schema.createTable('users', table => {
    table.uuid('id').notNullable().defaultTo(knex.raw('gen_random_uuid ()')).primary()
    table.string('email').notNullable()
    table.string('password').notNullable()
    table.string('personal_id').notNullable()
    table.string('two_fa').nullable()
    table.boolean('notify').nullable().defaultTo(false)
    table.boolean('changed_email').notNullable().defaultTo(false)
    table.timestamp('changed_password_at').nullable().defaultTo(null)
    table.timestamp('created_at').defaultTo(knex.fn.now())
    table.timestamp('updated_at').defaultTo(knex.fn.now())
  })
};

users_info表创建代码 #

exports.up = function(knex) {
  return knex.schema.createTable('users_info', table => {
    table.uuid('id').notNullable().defaultTo(knex.raw('gen_random_uuid ()')).primary()
    table.uuid('user_id')
      .references('id')
      .inTable('users')
      .notNullable()
    table.string('username').notNullable()
    table.string('first_name').nullable()
    table.string('last_name').nullable()
    table.boolean('show_email').notNullable().defaultTo(false)
    table.timestamp('created_at').defaultTo(knex.fn.now())
    table.timestamp('updated_at').defaultTo(knex.fn.now())
  })
};

我现有一个关联查询代码：

const result = knex(tableName)
      .leftJoin('users_info', 'users_info.user_id', 'users.id')
      .where('users.personal_id', personalId)
      .first(
        'users.personal_id as personalId',
        'users_info.first_name',
        'users_info.last_name',
      )

需求是：仅当users_info.show_email为true时，才返回users.email字段，如何用Knex实现这个PostgreSQL的条件查询？

可以通过Knex结合PostgreSQL的条件逻辑实现，以下是两种常用方式：

方式一：直接用knex.raw写CASE表达式 #

在first方法中添加一个条件字段，当show_email为true时返回email，否则返回null：

const result = knex('users')
      .leftJoin('users_info', 'users_info.user_id', 'users.id')
      .where('users.personal_id', personalId)
      .first(
        'users.personal_id as personalId',
        'users_info.first_name',
        'users_info.last_name',
        knex.raw(`CASE WHEN users_info.show_email = TRUE THEN users.email ELSE NULL END as email`)
      )

方式二：使用Knex链式when语法 #

用Knex原生的条件方法构建字段，写法更贴合框架风格：

const result = knex('users')
      .leftJoin('users_info', 'users_info.user_id', 'users.id')
      .where('users.personal_id', personalId)
      .first(
        'users.personal_id as personalId',
        'users_info.first_name',
        'users_info.last_name',
        knex.select(knex.ref('users.email')).where('users_info.show_email', true).as('email')
      )

说明 #

• 两种方式最终都会生成PostgreSQL的条件查询语句，当show_email为true时返回email，否则返回null（返回null比不返回字段更便于前端统一处理）。
• 原代码中的tableName建议替换为users，否则关联查询的逻辑会出现问题。

内容的提问来源于stack exchange，提问作者dokichan