Trouble doing simple PostgreSQL subquery using TypeORM

Question

Having issues trying to understand how I can turn this SQL into a TypeORM subquery.

SELECT id
FROM user
WHERE (SELECT COUNT(*)
   FROM book
   WHERE id = book.authorId AND status = 'published') > 0;

The idea for this particular subquery (the SELECT COUNT(*)) is to only return the users who have published at least one book. I explicitly want to do this using subquery. However, I am having trouble understanding how one would transform SELECT COUNT(*) to a TypeORM chained condition and then perform the comparison.

Context code:

const query = this.userRepository
  .createQueryBuilder('user')
  .leftJoin('user.listing', 'listing')
  .where(a_condition)
  .andWhere((qb) => { <what now> ? })

}

Sorry if the question is poorly explored, I have a "critical" bug in production and I am not a regular user of either TypeORM or PostgreSQL.

Answer 1

After battling for a while, I finally found the answer to my own issue.

The code works with a simple replacement of Insert TypeORM SubQuery with:

   ...previousQueryBuilderExpressions
   .andWhere((qb) => {
      const subquery = qb
        .subQuery()
        .select('COUNT(*)')
        .from(Book, 'book')
        .where('book.authorId = user.id')
        .andWhere(`book.status = :publish`, {
          publish: BookStatusTypes.PUBLISHED,
        })
        .getQuery();

      const condition = `${subquery} > 0`;

      return condition;
    });