Mikro-orm: How to get results from queries with aggregates into their own entity?

Issue

suppose i have User and Post entities with User one-to-many Post . I want to fetch a list of users with id, username, post_count. The sql query for this would look like this:

select u.id, u.username, count(p.id)
from users u left join posts p on u.id = p.user_id
group by u.id

I want to fetch the results of this query into its own entity, like

class PostsCountPerUser {
  id: string;
  username: string;
  count: number
}

How can i do this via mikro-orm?

Solution

There is no support for view entities, which is pretty much what you are asking for - you can subscribe here:

https://github.com/mikro-orm/mikro-orm/issues/672

edit: nowadays (v5) there are also virtual entities

You can do the query you want via query builder (or execute a raw SQL directly), and get the result as POJO via qb.execute():

const res = await em.createQueryBuilder(User, 'u')
  .select(['u.id', 'u.username', 'count(p.id) as count'])
  .leftJoin('u.posts', 'p')
  .groupBy('u.id')
  .execute();

Or as the User entity via qb.getResult(). For this approach you also need to define a non-persistent property to which you can then map the QB result - so in this case count.

@Entity()
class User {
  @Property({ persist: false })
  count?: number;
}

const users = await em.createQueryBuilder(User, 'u')
  .select(['u.id', 'u.username', 'count(p.id) as count'])
  .leftJoin('u.posts', 'p')
  .groupBy('u.id')
  .getResult();

In v4 you could also use @Filter() for this, where you could define a subquery that will give you the count:

https://github.com/mikro-orm/mikro-orm/blob/dev/docs/docs/defining-entities.md#formulas

Answered By - Martin Adámek