How to ORDER BY a column from INNER JOIN

Question

Table structure from comments:

id | user_id | cmt_id | slug 
 1 |   565   |   5    | home
 2 |   324   |   6    | home
 3 |   71    |   7    | home
 4 |   408   |   1    | about

Table structure from cmt_likes:

id | user_id | cmt_id | slug 
 1 |   324   |   6    | home
 2 |   324   |   6    | home
 3 |   324   |   6    | home
 4 |   71    |   7    | home
 5 |   71    |   7    | home

As you can see on the table cmt_likes, on the home page the comment from the user 324 have 3 likes, and the comment from the user 71 have 2 likes (i use the number of rows, that have the same cmt_id and slug to count the likes).

This is my current sql, this is only for display the comments:

SELECT 
  `comments`.`user_id`, `comments`.`cmt`, `comments`.`cmt_id`, `comments`.`slug`, `users`.`username`
FROM `comments` 
  INNER JOIN `users` 
    ON `comments`.`user_id` = `users`.`user_id` 
WHERE `comments`.`slug` = :slug
ORDER BY `comments`.`id` DESC

But i want to ORDER BY the number of likes.

so i tried:

SELECT 
  `comments`.`user_id`, `comments`.`cmt`, `comments`.`cmt_id`, `comments`.`slug`, `cmt_likes`.`cmt_id`, `users`.`username` 
FROM `comments` 
  INNER JOIN `users`
    ON `comments`.`user_id` = `users`.`user_id`
  INNER JOIN `cmt_likes` 
    ON `comments`.`cmt_id` = `cmt_likes`.`cmt_id` 
WHERE `comments`.`slug` = :slug
GROUP BY `cmt_likes`.`cmt_id` 
ORDER BY `cmt_likes`.`cmt_id` DESC

But this sql only return the rows that have 'likes'. If you look at my tables you will see that the comment from the user 565 don't have likes, so this row is not returning in the above sql.

This is my current result with the above sql:

324
71

And this is what i expected:

324
71
565

score 1 · Accepted Answer · answered Apr 25 '19 at 00:26

You need left joins. I also recommend table aliases:

SELECT c.user_id, c.cmt, c.cmt_id, c.slug, 
       c.cmt_id, u.username 
FROM comments c LEFT JOIN
     users u
     ON c.user_user = u.user_id LEFT JOIN
     cmt_likes cl
     ON c.cmt_id = cl.cmt_id 
WHERE c.slug = :slug
GROUP BY c.user_id, c.cmt, c.cmt_id, c.slug, c.cmt_id, u.username 
ORDER BY cl.cmt_id DESC

However, I think you also need aggregation before the JOIN:

SELECT c.user_id, c.cmt, c.cmt_id, c.slug, 
       c.cmt_id, u.username 
FROM comments c LEFT JOIN
     users u
     ON c.user_user = u.user_id LEFT JOIN
     (SELECT cl.cmt_id, COUNT(*) as cnt
      FROM cmt_likes cl
      GROUP BY cl.cmt_id
     ) cl
     ON c.cmt_id = cl.cmt_id 
WHERE c.slug = :slug
GROUP BY c.user_id, c.cmt, c.cmt_id, c.slug, c.cmt_id, u.username 
ORDER BY cl.cmt_id DESC

Indeed, i only needed to change `INNER JOIN` to `LEFT JOIN` in my second sql. — mario, Apr 25 '19 at 00:40

How to ORDER BY a column from INNER JOIN

1 Answers1