typeorm: [Question] How to load relation and count entity and map it so it is not nested?

don’t harm yourself and your machine and execute a separate queries

So after many attempts, this is what I came up with that seems to product the desired output, do you see any ways to improve this or is this syntax acceptable?

    async findScriptsByAuthorId(authorId: number): Promise<any> {
        return await this.scriptRepository
            .createQueryBuilder('script')
            .leftJoin('script.author', 'author')
            .leftJoin('script.users', 'users')
            .leftJoin('script.sessions', 'sessions')
            .where('author.id = :id', { id: authorId })
            .select('script.id', 'id')
            .addSelect('script.name', 'name')
            .addSelect('script.key', 'key')
            .addSelect('script.created', 'created')
            .addSelect('COUNT(DISTINCT(users.id)) as users')
            .addSelect('COUNT(DISTINCT(sessions.id)) as sessions')
            .groupBy('script.id')
            .getRawMany();
    }

[
    {
        "id": 1,
        "name": "Script Name",
        "key": "130bbbf9-5c17-488f-8143-dba67a20d512",
        "created": "2018-05-09T14:06:08.933Z",
        "users": "1",
        "sessions": "10"
    },
    {
        "id": 2,
        "name": "Test",
        "key": "45ceb0d9-a3bc-4278-817b-4da2dc4e949b",
        "created": "2018-07-10T22:46:42.649Z",
        "users": "0",
        "sessions": "0"
    }
]

Only issue I have with it now is COUNT() is being returned as a string “10” instead of a number, are there anyways around this? Is this related to https://github.com/typeorm/typeorm/issues/1875

Sorry but I don’t understand what exact issue is?

btw don’t use loadRelationCountAndMap method - it will be removed in the future.

What you did it is acceptable, but I usually do it different. I load my entities, and they execute separate small queries on counts and store execution result in entity properties.

It seems working

      .innerJoinAndSelect('G.members', 'M')
      .addSelect('COUNT(M.id)', 'memberCount')
      .groupBy('G.id')
      .addGroupBy('M.id')
      .orderBy({ memberCount: 'DESC' })

But I just want this

.loadRelationCountAndMap('G.members','memberCount')
.orderBy({ memberCount: 'DESC' })

@Diluka just

this.createQueryBuilder('a')
                .select('a.id')
                .addSelect((subQuery) => {
                    return subQuery
                        .select('COUNT(c.id)', 'count')
                        .from(Comment, 'c')
                        .where('c.article.id = a.id');
                }, 'count')
                .orderBy('count', 'DESC')
                .loadRelationCountAndMap('a.comment_count', 'a.comments')

both has orderBy and Count

Select relation with count:

  var manager = getManager().getRepository("tipos_derechos").createQueryBuilder("tipos_derechos")
            .leftJoinAndSelect('tipos_derechos.derechos', 'derechos')
            .loadRelationCountAndMap('tipos_derechos.derechos_count', 'tipos_derechos.derechos')

[
  {
    "id": 1,
    "nombre": "",
    "derechos": [
      {
        "id": 1,
        "id_tipo_derecho": 1,
        "num_orden": null
      }
    ],
    "derechos_count": 1
  }
]

When I run such a query in postgres, the calculated count field appears in a property called “undefined” and is not using the provided ‘mapToProperty’ parameter.

You will have to provide the alias in mapToProperty.

Wrong

const user = await queryBuilder.select([
          'user.id',
          'user.username',
          'user.fullName',
          'user.bio',
          'user.profilePicture',
        ])
        .loadRelationCountAndMap('followers', 'user.followers') <--- Notice the mapToProperty "followers" without alias      
        .where('user.id = :userId', { userId })
        .getOne()

Correct

const user = await queryBuilder.select([
          'user.id',
          'user.username',
          'user.fullName',
          'user.bio',
          'user.profilePicture',
        ])
        .loadRelationCountAndMap('user.followers', 'user.followers') <--- Added alias to mapToProperty
        .where('user.id = :userId', { userId })
        .getOne()

@pleerock I am still struggling on how I would structure this into separate queries as I need to return a list of Script[]

I was able to wrap my head around a single query on a script.id and getting the data count and appending it to the Script entity on a virtual property… I dont quite understand how I can add an additional query onto the current search.

In order to get the data, I have to use a relation from script -> sessions -> data

So far I have narrowed it down to:

        return await this.scriptRepository
            .createQueryBuilder('script')
            .leftJoinAndSelect('script.sessions', 'sessions')
            .loadRelationCountAndMap('script.users', 'script.users')
            .loadRelationCountAndMap('script.sessions', 'script.sessions')
            .getMany();

[
    {
        "id": 1,
        "name": "Script 1",
        "sessions": 1,
        "users": 1
    },
    {
        "id": 2,
        "name": "Script 2",
        "sessions": 0,
        "users": 0
    }
]

I need

[
    {
        "id": 1,
        "name": "Script 1",
        "sessions": 1,
        "users": 1
        "data": 5
    },
    {
       ...
    }
]

Still not removed 😉

execute separate queries using multiple query builders. Just think what SQL query you want to execute, execute it, get desired result and store it inside class property