Query with a sub query that requires multiple values

Question

I can't really think of a title so let me explain the problem:

Problem: I want to return an array of Posts with each Post containing a Like Count. The Like Count is for a specific post but for all users who have liked it

For example:

const posts = [
  {
    post_id: 1,
    like_count: 100
  },
  {
    post_id: 2,
    like_count: 50
  }
]

Now with my current solution, I don't think it's possible but here is what I have so far.

My query currently looks like this (produced by TypeORM):

SELECT
   "p"."uid" AS "p_uid",
   "p"."created_at" AS "post_created_at",
   "l"."uid" AS "like_uid",
   "l"."post_liked" AS "post_liked",
   "ph"."path" AS "path",
   "ph"."title" AS "photo_title",
   "u"."name" AS "post_author",
   (
      SELECT
         COUNT(like_id) AS "like_count" 
      FROM
         "likes" "l" 
         INNER JOIN
            "posts" "p" 
            ON "p"."post_id" = "l"."post_id" 
      WHERE
         "l"."post_liked" = true 
         AND l.post_id = $1
   )
   AS "like_count" 
FROM
   "posts" "p" 
   LEFT JOIN
      "likes" "l" 
      ON "l"."post_id" = "p"."post_id" 
   INNER JOIN
      "photos" "ph" 
      ON "ph"."photo_id" = "p"."photo_id" 
   INNER JOIN
      "users" "u" 
      ON "u"."user_id" = "p"."user_id"

At $1 is where the post.post_id should go (but for the sake of testing I stuck the first post's id in there), assuming I have an array of post_ids ready to put in there.

My TypeORM query looks like this

  async findAll(): Promise<Post[]> {
    return await getRepository(Post)
    .createQueryBuilder('p')
    .select(['p.uid'])
    .addSelect(subQuery => 
      subQuery
        .select('COUNT(like_id)', 'like_count')
        .from(Like, 'l')
        .innerJoin('l.post', 'p')
        .where('l.post_liked = true AND l.post_id = :post_id', {post_id: 'a16f0c3e-5aa0-4cf8-82da-dfe27d3f991a'}), 'like_count'
    )
    .addSelect('p.created_at', 'post_created_at')
    .addSelect('u.name', 'post_author')
    .addSelect('l.uid', 'like_uid')
    .addSelect('l.post_liked', 'post_liked')
    .addSelect('ph.title', 'photo_title')
    .addSelect('ph.path', 'path')
    .leftJoin('p.likes', 'l')
    .innerJoin('p.photo', 'ph')
    .innerJoin('p.user', 'u')
    .getRawMany()
  }

Why am I doing this? What I am trying to avoid is calling count for every single post on my page to return the number of likes for each post. I thought I could somehow do this in a subquery but now I am not sure if it's possible.

Can someone suggest a more efficient way of doing something like this? Or is this approach completely wrong?

Answer 1

I find working with ORMs terrible and cannot help you with this. But the query itself has flaws:

1. You want one row per post, but you are joining likes, thus getting one row per post and like.
2. Your subquery is not related to your main query. It should instead relate to the main query's post.

The corrected query:

SELECT
   p.uid,
   p.created_at,
   ph.path AS photo_path,
   ph.title AS photo_title,
   u.name AS post_author,
   (
      SELECT COUNT(*)
      FROM likes l 
      WHERE l.post_id = p.post_id
      AND l.post_liked = true 
   ) AS like_count 
FROM posts p 
JOIN photos ph ON ph.photo_id = p.photo_id 
JOIN users u ON u.user_id = p.user_id
ORDER BY p.uid;

I suppose it's quite easy for you to convert this to TypeORM. There is nothing wrong with counting for every single post, by the way. It is even necessary to get the result you are after.

The subquery could also be moved to the FROM clause using GROUP BY l.post_id within. As is, you are getting all posts, regardless of them having likes or not. By moving the subquery to the FROM clause, you could instead decide between INNER JOIN and LEFT OUTER JOIN.

The query would benefit from the following index:

CREATE INDEX idx ON likes (post_id, post_liked);

Provide this index, if the query seems too slow.