TypeORM Postgres filter by relation count

Question

context I have Users collection and Recipe collection, ManyToMany relation between them

I'm new in this framework, wondering how can I do the following query: count users with at least one recipe count users without any recipes

I have found loadRelationCountAndMap is very useful in counting how many recipes a user has, but I can't seem to filter the total response according to this property. I have tried this:

const users_without_recipes = await getRepository(User)
                .createQueryBuilder('user')
                .addSelect(['user.createdAt', 'user.email'])
                .loadRelationCountAndMap('user.recipes_count', 'user.recipes')
                .where('user.recipes_count = :count', {count: 0})
                .getManyAndCount();

also tried to use postgres array_count but not sure how to integrate it with the typeORM framework and help is very appreciated

Answer 1

You can do this with subqueries I think.

Something like this in SQL:

SELECT *
// ... other stuff
WHERE user.id IN (
    SELECT u.id
    FROM user u JOIN recipie r USING(id)
    GROUP BY u.id
    HAVING COUNT(*) > 10
)

Or in TypeORM:

// ...
.where(qb => {
    const subQuery = qb.subQuery()
        .select("u.id")
        .from(User, "u")
        // join users and recipies 
        .groupBy("u.id")
        .having("COUNT(*) > :count", { count: 10 })
        .getQuery();
    return "user.id IN " + subQuery;
});
//...

Answer 2

I have stumbled upon this exact problem myself. I have fond a couple of sources that could help you find the solution. Your underlying problem is more general. If you turn on logging, you will probably see an error message something like:
Error column "user"."recipes_count" does not exist
The problem is that you are trying to use an alias. This question deals with this problem: Using an Alias in a WHERE clause

I hope you are more successful then me, but I decided to use a workaround after a log trial and error. I am sure there is a better way. If you manage to find it, please let me know.
(If you want to get back the list of user entities, without the user.recipes_count property, include the last .map as well.)

const users_without_recipes =  await getRepository(User)
.createQueryBuilder('user')
.loadRelationCountAndMap('user.recipes_count', 'user.recipes')
.getMany();

return users_without_recipes 
    .filter((user) => user['user.recipes_count'] === 0)
    .map(({recipes_count, ...otherProperties}) => otherProperties);