CODEWITHSUNDEEP
phpmysqlsqlcommentssql-order-by
user3835276
user3835276

Reputation: 21

How to sort records by most recent records in other table

I am trying to order statements by most recent activity (comments). However if a statement doesn't have a comment, the below query doesn't display that statement at the end. How can I change the query, so statements with no comments are displayed and displayed last?

SELECT 
  `statements`.`id`,
  `statement`,
  `statements`.`timestamp`,
  `statements`.`published`,
  `image`,
  `statements`.`author_id`,
  `statements`.`ip_address`,
  `username`,
  `comment_count` 
FROM
  (`statements`) 
  INNER JOIN `comments` 
    ON `comments`.`statement_id` = `statements`.`id` 
  LEFT JOIN `users` 
    ON `users`.`id` = `statements`.`author_id` 
WHERE `statements`.`published` > 0 
GROUP BY `statements`.`id` 
ORDER BY MAX(comments.id)

Upvotes: 0

Views: 58

Answers (2)

Nicolai
Nicolai

Reputation: 5787

Try LEFT JOIN for comments table:

SELECT `statements`.`id`, `statement`, `statements`.`timestamp`, `statements`.`published`, `image`, `statements`.`author_id`, `statements`.`ip_address`, `username`, `comment_count`
FROM (`statements`) 
    LEFT JOIN `comments` ON `comments`.`statement_id` = `statements`.`id` 
    LEFT JOIN `users` ON `users`.`id` = `statements`.`author_id` 
WHERE `statements`.`published` > 0
GROUP BY `statements`.`id` 
ORDER BY MAX(comments.id) DESC

Ordering descending by maximal comment id will put statement with most recent comment at top.

Regarding MySQL documentation Working with NULL Values

When doing an ORDER BY, NULL values are presented first if you do ORDER BY ... ASC and last if you do ORDER BY ... DESC.

statements without comments (MAX(comments.id) IS NULL) must be placed at the bottom of result.

Upvotes: 3

Bulat
Bulat

Reputation: 6979

I would do it slightly different altogether:

SELECT 
  statements.*,
  cm.id 
FROM
  statements LEFT JOIN
  (SELECT statement_id, MAX(id) id FROM comments GROUP BY statement_id) cm 
    ON cm.statement_id = statements.id
  LEFT JOIN users
    ON users.id = statements.author_id 
WHERE statements.published > 0 
ORDER BY cm.id;

This way you don't depend on MySQL group by hack and this query should work in any other database.

Upvotes: 0

Related Questions