GroupBy in MirkoORM

Question

I have an Entity that looks like this:

@Entity({ tableName: 'certificates' })
export class CertificateEntity extends BaseEntity {
  @Property({ type: 'float' })
  version: number;

  @Property()
  name: string;

  @Property()
  type: CertificateType;

  @Property()
  description: string;
}

I am trying to get a list of the latest certificates by version for every type. So only the highest version of each type should appear.

I am running

const r = await this.em.createQueryBuilder(CertificateEntity)
      .select(['type', 'version'])
      .groupBy('type')
      .execute();

But what I am getting is

{
    "statusCode": 500,
    "path": "/certificates/list",
    "errorType": "error",
    "errorMessage": "select \"c0\".\"type\", \"c0\".\"version\" from \"certificates\" as \"c0\" group by \"c0\".\"type\" - column \"c0.version\" must appear in the GROUP BY clause or be used in an aggregate function"
}

If I add the version as instructed by the error message. I just get the list

[
    {
        "version": 1.7,
        "type": "s-type"
    },
    {
        "version": 1.6,
        "type": "s-type"
    },
    {
        "version": 1.5,
        "type": "s-type"
    }
]

What am I doing wrong?

Answer 1

The error says "must appear in the GROUP BY clause or be used in an aggregate function", and for what you want to do, the second part is valid, not the first one. You want to use an aggregate function for the columns you want to select but not group by them. This is because the grouping can produce different values for the version - and based on what you say you want, you should be selecting max(version) and not just version.

Try this instead:

const r = await this.em.createQueryBuilder(CertificateEntity)
      .select(['type', sql`max(version)`])
      .groupBy('type')
      .execute();

sql is a helper exported from he @mikro-orm/core or any other driver package, see https://mikro-orm.io/docs/raw-queries