Multiple count and left joins in MySQL Node using knex

Question

I am trying to query posts and use 'count' to get the total amount of comments and likes to display. My query looks like this

  const posts = await knex
    .from("posts")
    .select("posts.id as id", "posts.text", "posts.user_id")
    .leftJoin("comments", "comments.post_id", "posts.id")
    .count("comments.post_id as comments")
    .leftJoin("likes", "likes.post_id", "posts.id")
    .count("likes.post_id as likes")
    .groupBy("posts.id");

  res.send(posts);

However, I get different results if I exclude comments or likes and do something like this:

  const posts = await knex
    .from("posts")
    .select("posts.id as id", "posts.text", "posts.user_id")
    .leftJoin("comments", "comments.post_id", "posts.id")
    .count("comments.post_id as comments")
    .groupBy("posts.id");

  res.send(posts);

I feel like I am doing something wrong. What is the correct way to chain multiple 'count' and 'leftJoins'?

Answer 1

Firstly start with SQL query and then convert it to Knex.

As @nbk said, when you joining the comments at the final result you will receive a row for each comment.

One option is using sub-query in select, the query will look like:

Select posts.id as id, posts.text, posts.user_id, 
(Select count(*) from comments where comments.post_id=posts.id) as comments,
(Select count(*) from likes where likes.post_id=posts.id) as likes,
From posts;

This query can be converted to Knex:

const posts = await knex
  .from('posts')
  .select(
    'posts.id as id',
    'posts.text',
    'posts.user_id',
    knex('comments')
      .count('*')
      .whereRaw('?? = ??', ['comments.post_id', 'posts.id'])
      .as('comments'),
    knex('likes').count('*').whereRaw('?? = ??', ['likes.post_id', 'posts.id']).as('likes')
  );