mikro-orm

Common Table Expressions - is there a way to do them in MikroOrm?

Is there a way (even a hacky way) to do common table expressions with MikroOrm? I've searched all over and can't even find any post mentioning it whatsoever.

Solution

  • You can use CTE's with Mikro-ORM. Mikro-ORM built on top of Knex.js query builder that supports it. Here is an example:

      const knex = em.getKnex();
  const result = await knex
    .with('cte_name', ['participant_id', 'sum_minutes'], (qb) =>
      qb
        .from('attendance')
        .select('participant_id', knex.raw('sum(minutes) as sum_minutes'))
        .groupBy('participant_id'),
    )
    .select('*')
    .from('cte_name')
    .orderBy('sum_minutes', 'DESC');

    if you need to map raw results to Mikro-ORM entities, you can use em.map()

    const entities = result.map((r) => em.map(EntityType, r));