CODEWITHSUNDEEP
mysqldoctrineforeign-keyssql-order-by
tamir
tamir

Reputation: 3297

Order by one-to-many relation

I have 2 tables:

Message:
    id:         INT
    created_at: DATETIME

Comment:
    id:         INT
    message_id: INT
    created_at: DATETIME

One Message has many Comments.

I want to get all Messages ordered by the most recent activity:

So basically, I want to sort it like a classic e-mail or private messaging system.

Maybe I can INNER JOIN the Comments, but I don't think it's necessary to get all the Comments just for the sort.

Also, I thought about creating a column in Message to save the last activity date and update it whenever a Comment is created, but I'd like to see if you have better solutions..

I'm using Doctrine, so if you have a Doctrine-based solution I'd rather that,

Thanks!

Upvotes: 2

Views: 3408

Answers (3)

Bez Hermoso
Bez Hermoso

Reputation: 1132

What I would do in MySQL is something like:

SELECT *
FROM Messages AS m
ORDER BY GREATEST(
   m.created_at, 
   (SELECT MAX( c.created_at ) FROM Comment AS c WHERE c.message_id = m.id )
) DESC

Upvotes: 2

Hong Ning
Hong Ning

Reputation: 1003

Try something like this:

select m.message_id, c.created_at, m.created_at 
from message m
left join (
    select message_id, MAX(created_at) as created_at
    from comment
    group by message_id
) c on m.id = c.message_id
order by 
    ( case when c.created_at >= m.created_at then c.created_at
          else m.created_at
     end ) desc

The subquery will get most current comment date for each message, then message table will outer join the subquery because some message might not have any comments at all. Then order by greater value of comment created date and message created date.

You might need to change the case syntax for mysql.

Upvotes: 1

Daniel Pereira
Daniel Pereira

Reputation: 1785

If your comment table's id is an auto-incrment you can simply order by id desc and it will return the results in chronological order as long as you aren't doing deletes on the comment table which has a tendency to make inserts show up out of chronological order. Otherwise, you need to do something like:

SELECT a.id, a.created_at 
FROM Message a, Comment b 
WHERE b.created_at > a.created_at 
ORDER BY b.created_at DESC

Upvotes: 0

Related Questions