typeorm 按上次 createdAt 选择 OneToMany 实体

Question

我认为有一个非常典型的问题 -OneToMany通过 typeorm选择关系的最新实体的正确方法是什么？数据库是postgresql

@Entity('hubs')

export class Hub {

    @PrimaryGeneratedColumn('uuid')

    id: string;

    @Column()

    ip: string;

    @OneToMany(type => HubStatus, statusLog => statusLog.hub, { cascade: true })

    statusLog: Array<HubStatus>;

    @UpdateDateColumn()

    updatedAt: Date;

    lastStatus: any;

    @AfterLoad()

    async getStatus() {

          const query = createQueryBuilder(Hub)

            .leftJoinAndSelect("Hub.statusLog", "statusLog", 'Hub.id = :id', { id: this.id })

            //.addSelect("MAX(statusLog.createdAt)")

            //.andHaving('statusLog.createdAt = MAX(statusLog.createdAt)');

            //.andWhere('Hub.id = :id', { id: this.id })

            //.andWhere('statusLog.createdAt = MAX(statusLog.createdAt)');

          //assign part of result to this.lastStatus

    }

}

@Entity('hub-status')

export class HubStatus {

    @PrimaryGeneratedColumn()

    id: string;

    @Column({type: 'json')

    somecolumn

    @ManyToOne(type => Hub, hub => hub.statusLog)

    hub: Hub;

    @CreateDateColumn()

    createdAt: Date;

}

我想获得相关的最新（通过createdAt）HubStatus的@AfterLoad块。max不能在where子句中使用，我也尝试了使用maxin 的多种选项，select但由于不同的原因它们失败了。

Answer 1

我是这样做的，所以它至少可以用于开发目的：@AfterLoad()async getStatus() {&nbsp; &nbsp; const innerSelect = getRepository(HubStatus)&nbsp; &nbsp; &nbsp; &nbsp; .createQueryBuilder()&nbsp; &nbsp; &nbsp; &nbsp; .select('id')&nbsp; &nbsp; &nbsp; &nbsp; .where('HubStatus.hub.id = :id')&nbsp; &nbsp; &nbsp; &nbsp; .orderBy('id', "DESC")&nbsp; &nbsp; &nbsp; &nbsp; .limit(1);&nbsp; &nbsp; const query = createQueryBuilder(Hub)&nbsp; &nbsp; &nbsp; &nbsp; .select('somecolumn')&nbsp; &nbsp; &nbsp; &nbsp; .leftJoin(`Hub.statusLog`, "statusLog", `statusLog.id = (${innerSelect.getSql()})`, { id: this.id })&nbsp; &nbsp; this.lastStatus = (await query.getRawOne()).somecolumn;}但问题仍然存在 - 可能会有数百万个条目HubStatus，如果认为ORDER BY+LIMIT 1解决方案在这种情况下不会表现良好。

Answer 2

另一种方法是在连接条件中使用leftJoinAndMapOne查询构建器方法和NOT EXISTSSQL 条件来测试以后的实体：&nbsp; &nbsp; &nbsp; const query = createQueryBuilder(Hub)&nbsp; &nbsp; &nbsp; &nbsp; .leftJoinAndMapOne('Hub.statusLog', HubStatus, 'statusLog',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;`Hub.id = statusLog.hubId AND NOT EXISTS (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SELECT 1 FROM hub-status hs1 WHERE hs1.hubId = Hub.id AND hs1.id > statusLog.id)`,&nbsp; &nbsp; &nbsp; &nbsp; ).getMany()