使用ObjectionJS与Knex一对多查询：获取每个会话第一条关联消息

方案1：匹配你提供的原生SQL的写法 #

直接把你的原生SQL逻辑转成Objection链式调用即可：

await Conversation.query()
  .alias('u')
  .select('u.id as idConversation', 'p.text')
  // 关联时走子查询匹配单条消息id
  .innerJoin('Message as p', function () {
    this.on(
      'p.id', 
      '=',
      // 如果需要取最新/最早的消息，在子查询里加 ORDER BY date desc/asc 即可
      Conversation.knex().raw('(SELECT id FROM Message as p2 WHERE p2.idConversation = u.id LIMIT 1)')
    )
  })
  .where('u.status', 'open')

查询结果会直接返回你预期的扁平结构。 #

方案2：符合Objection ORM规范的写法 #

首先需要提前在Conversation模型中定义一对多关联：

// Conversation 模型定义文件
const { Model } = require('objection')
const Message = require('./Message')

class Conversation extends Model {
  static get tableName() {
    return 'Conversation'
  }

  static get relationMappings() {
    return {
      messages: {
        relation: Model.HasManyRelation,
        modelClass: Message,
        join: {
          from: 'Conversation.id',
          to: 'Message.idConversation'
        }
      }
    }
  }
}

查询时使用关联预加载+修饰器限制单条：

await Conversation.query()
  .where('status', 'open')
  .withGraphFetched('messages(limitSingle)')
  .modifiers({
    limitSingle(builder) {
      // 同样可以加 orderBy 控制取最新/最早的消息
      builder.select('idConversation', 'text').limit(1)
    }
  })

返回结果中每个会话对象会携带messages数组，数组内仅包含1条匹配的消息，你可以按需做字段拍平处理。 #

原写法报错原因说明 #

你之前使用的distinct('Message.idConversation')不生效，是因为distinct作用于你select的所有字段的组合值，你同时选了idConversation和text，同个会话的不同消息组合后值不重复，所以会返回多条记录。

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