select OneToMany entity by last createdAt

[opahopa]

Issue type:

[x] question [ ] bug report [ ] feature request [ ] documentation issue

Database system/driver:

[ ] cordova [ ] mongodb [ ] mssql [ ] mysql / mariadb [ ] oracle [x] postgres [ ] cockroachdb [ ] sqlite [ ] sqljs [ ] react-native [ ] expo

TypeORM version:

[x] latest [ ] @next [ ] 0.x.x (or put your version here)

Have quite typical problem i think - what is the proper way to select the latest entity of OneToMany relationship by typeorm?

schema:

@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;
}

i want to get the latest (by createdAt) associated HubStatus in @AfterLoadhook. max can't be used in where clause, i also tried multiple options of using max in select and they fail for different reasons.

[chenwenqin]

+1