Count relations in Typeorm and then use the result column

I have an entities User and Post with many-to-many relation (table with postId and userId).

I need to find all users without given postId.

leftjoin with exclude id doesn't work, because if user have multiple posts, then it always returns user.

So far, loadRelationCountAndMap is a best result (it returns counts)

const builder = this.userRepository
    .createQueryBuilder('user')
    .loadRelationCountAndMap(
        'user.userPosts',
        'user.posts',
        'userPosts',
        (qb) => {
            return qb.andWhere(
                'userPosts.userId != :excludePostId',
                { excludePostId },
            );
        },
    );

But i can't do something like this

builder.andWhere('userPosts > 0');

It cause en error, Column not found.

Also try this

builder.innerJoin('user.posts', 'userPosts')
    .addSelect('COUNT(userPosts.userId)', 'userPostsCount')
    .groupBy('user.id')
    .addGroupBy('userPosts.userId')
    .addGroupBy('userPosts.postId')

Column exist in rawQuery but still, when i try

builder.andWhere('userPostsCount > 0');

Same error Column not found

You could try query using NOT EXISTS

const users = await this.userRepository
  .createQueryBuilder('user')
  .where(qb => {
    const subQuery = qb.subQuery()
      .select('1')
      // your join table name (check actual name)
      .from('user_posts', 'up') 
      .where('up.userId = user.id')
      .andWhere('up.postId = :postId')
      .getQuery();
    return `NOT EXISTS ${subQuery}`;
  })
  .setParameter('postId', excludePostId)
  .getMany(
);