TypeORM中innerJoinAndMapOne关联子查询报错原因咨询

实体定义 #

Channel 实体 #

@Entity({ name: 'channels' })
export class Channel extends BaseEntity {
  @Index()
  @Column({ name: 'topic', unique: true, nullable: true })
  topic: string;

  @Column({ name: 'description', nullable: true })
  description: string;

  @Column({ name: 'is_deleted', type: 'boolean', default: false })
  isDeleted: boolean;

  @Column({ name: 'is_group', type: 'boolean', default: false })
  isGroup: boolean;

  @OneToMany(() => Message, (message) => message.channel)
  channelMessages: Message[];

  @ManyToOne(() => User, (user) => user.channelsCreated)
  @JoinColumn({ name: 'created_by' })
  createdBy: User;

  @OneToMany(() => ChannelUser, (channelUser) => channelUser.channel, {
    cascade: true,
  })
  users: ChannelUser[];

  latestMessage : Message;
}

Message 实体 #

@Entity({ name: 'messages' })
export class Message extends BaseEntity {
  @Column({ name: 'content' })
  content: string;

  @ManyToOne(() => User, (user) => user.sentMessages)
  @JoinColumn({ name: 'sender_id' })
  sender: User;

  @ManyToOne(() => Channel, (channel) => channel.channelMessages)
  @JoinColumn({ name: 'channel_id' })
  channel: Channel;

  @Column({ name: 'is_deleted', type: 'boolean', default: false })
  isDeleted: boolean;

  @Column({
    name: 'status',
    type: 'enum',
    enum: MessageStatusEnum,
    default: MessageStatusEnum.SENT,
  })
  status: MessageStatusEnum;

  @OneToMany(() => MessageStatus, (messageStatus) => messageStatus.message)
  readMessages: MessageStatus[];
}

尝试的查询代码 #

const channels = this.channelRepository
  .createQueryBuilder('c')
  .innerJoinAndMapOne(
    'c.latestMessage',
    (sub) => {
      return sub
        .from(Message, 'm')
        .orderBy({ 'm.createdAt': 'DESC' })
        .limit(1);
    },
    'latestMessage',
    'latestMessage.channel = c.id',
  );

return channels.getMany();

错误信息 #

QueryFailedError: missing FROM-clause entry for table "latestmessage"

原因及解决方法 #

问题根源 #

你的子查询没有和主查询的频道做关联，它只是全局查询了一条最新消息，而外部的关联条件latestMessage.channel = c.id无法将子查询结果与每个频道绑定。数据库解析时，找不到子查询结果与主表的关联关系，因此报错。

另外，关联条件里用了实体属性名channel，但数据库实际字段是channel_id，这也会导致匹配失败。

解决方法1：子查询关联单个频道的最新消息 #

直接在子查询中过滤当前频道的消息，确保每个频道只取自己的最新消息：

const channels = this.channelRepository
  .createQueryBuilder('c')
  .leftJoinAndMapOne(
    'c.latestMessage',
    (subQuery) => {
      return subQuery
        .select('m.*')
        .from(Message, 'm')
        .where('m.channel_id = c.id') // 关联当前频道
        .orderBy('m.createdAt', 'DESC')
        .limit(1);
    },
    'latestMessage',
    'latestMessage.channel_id = c.id'
  )
  .getMany();

解决方法2：使用窗口函数批量筛选所有频道的最新消息 #

如果频道数量较多，窗口函数的性能更优，它可以一次性计算出所有频道的最新消息：

const channels = this.channelRepository
  .createQueryBuilder('c')
  .leftJoinAndMapOne(
    'c.latestMessage',
    (sub) => {
      // 先给每个频道的消息按创建时间排序，标记行号
      return sub
        .from((innerSub) => {
          return innerSub
            .select([
              'm.*',
              'ROW_NUMBER() OVER (PARTITION BY m.channel_id ORDER BY m.createdAt DESC) AS rn'
            ])
            .from(Message, 'm');
        }, 'ranked_messages')
        .where('ranked_messages.rn = 1'); // 只保留每个频道的第一条消息
    },
    'latestMessage',
    'latestMessage.channel_id = c.id'
  )
  .getMany();

注意：如果要确保获取未删除的消息，可以在子查询中添加.where('m.is_deleted = false')条件。

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