Typeorm composite key: query only newest version

user_1059828 0
Viewed 138

I have a composite primary key of uuid and version. What would be the best practice of querying only the newest version of all entities matching a certain where clause? I probably need some kind of distinct clause ... is this possible using active record pattern?

   // returns all articles ordered by version, but I need only the newest version of each Article 
   Article.find({
      where: {
        company: '35fccfba-b4f8-4393-b211-c258d0a79076',
        status: 'approved',
      },
      order: {
        version: 'DESC',
      });

Table looks like:

uuid version status company
50ea6422-f56e-45e2-... 1 APPROVED 35fccfba-b4f8-4393-b211-c258d0a79076
8e6d646b-4772-47c6-... 1 APPROVED 35fccfba-b4f8-4393-b211-c258d0a79076
93b06cef-0d94-493b-... 1 APPROVED 35fccfba-b4f8-4393-b211-c258d0a79076
0094d9da-1768-4b8b-... 1 APPROVED 35fccfba-b4f8-4393-b211-c258d0a79076
0094d9da-1768-4b8b-... 0 APPROVED 35fccfba-b4f8-4393-b211-c258d0a79076
3bf2a3b3-e201-4bb1-... 0 APPROVED 35fccfba-b4f8-4393-b211-c258d0a79076
875c1145-a793-4243-... 0 APPROVED 35fccfba-b4f8-4393-b211-c258d0a79076
a2c63577-2bff-4104-... 0 APPROVED 35fccfba-b4f8-4393-b211-c258d0a79076
typeorm-activerecord typeorm
1 Answers

Edited due to misreading the question

What you are trying to achieve is "select latest" or "select highest" for each entity. This can be done, but the solution may become Database dependent or be inefficient.

The query you need to execute (assuming you are using a SQL DB) is:

select a.*
from articles a
inner join (
    select uuid, max(version) as version
    from articles group by uuid
) b
on a.uuid = b.uuid and a.version = b.version;

This can be done in TypeORM (with Active Record pattern):

@Entity()
export class Article extends BaseEntity {
    @PrimaryColumn()
    uuid: string;
    @PrimaryColumn()
    version: number;
    @Column()
    status: string;
    @Column()
    company: string;

    static latest(
        where?: Brackets | string | ObjectLiteral | ObjectLiteral[],
        parameters?: ObjectLiteral
    ): Promise<Article[] | undefined> {
        // Pivot table
        let pvt = this.createQueryBuilder('pvt')
            .select(['uuid', 'max(version) as version'])
            .groupBy('uuid');
        if (where) pvt = pvt.where(where, parameters);
        // Select
        return this.createQueryBuilder('art')
            .innerJoin(
                `(${pvt.getQuery()})`,
                'pvt',
                'art.uuid = pvt.uuid and art.version = pvt.version'
            )
            .setParameters(pvt.getParameters())
            .getMany();
    }
}

And used as follows:

Article.latest({
    company: '35fccfba-b4f8-4393-b211-c258d0a79076',
    status: 'approved',
})

Other solutions

An approach that is often neglected is to change data structure according to your query load.

The proposed solution becomes messy as complexity grows. This is why I would suggest you use a different approach. For example, keep a table that holds only the latest version and another that keeps all (or all except the last) versions. Hence, bend the data structure to better fit your query load.

user_54244260
Related