Typeorm composite key: query only newest version

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

Solution

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',
})

Typeorm composite key: query only newest version

Other solutions