Een makkelijke manier is denk ik om bij een conversatie bij te houden wat de laatste reactie (het laatste reactie-id) is.

Je slaat dan weliswaar redundante (afleidbare) informatie op, maar je queries worden dan volgens mij een stuk simpeler :). In dat geval kun je via een simpele INNER JOIN meteen de juiste message.reaction ophalen.

Meh, je zou het ook met een subquery kunnen oplossen:

SELECT ...
FROM conversations c, messages m
WHERE messages.id = (SELECT MAX(id) FROM messages WHERE conversationId = c.id)
AND ...
ORDER BY c.favourite, m.id DESC
LIMIT 0, 30

Er vanuitgaande dat er een messages.id kolom is. Moet je trouwens niet ook aflopend sorteren op c.favourite? Eerst de favoriete conversaties, dan de normale?

Sorteren kan ook op id, immers, de reactionDates volgen dezelfde sortering als id's (een reactie die later gegeven heeft heeft zowel een hoger id als een latere datum).

Mogelijk moet je nog kijken naar indexen (bijvoorbeeld op messages.conversationId, als je niet al van foreign keys gebruik maakt, in welk geval er al automatisch geindexeerd wordt) om de query efficient te houden.

Volgens mij kan het veel eenvoudiger, of ik zit met mijn vermoeide hoofd compleet verkeerd te denken:

SELECT C.name, MAX(M.date) AS last_reaction_date
FROM conversations AS C
JOIN messages AS M ON M.conversation_id=C.id
GROUP BY M.conversation_id

Dat zou een makkelijkere oplossing zijn geweest, ware het niet dat de gewenste informatie (onder andere) de laatste message van een conversation is (en niet enkel simpelweg de datum), volgens mij kun je niet garanderen dat je op die manier de laatstgeplaatste message terugkrijgt als je groepeert op datum...

But feel free to try :).

Citaat van FangorN

Dat zou een makkelijkere oplossing zijn geweest, ware het niet dat de gewenste informatie (onder andere) de laatste message van een conversation is (en niet enkel simpelweg de datum), volgens mij kun je niet garanderen dat je op die manier de laatstgeplaatste message terugkrijgt als je groepeert op datum...

But feel free to try :).

Daar had ik inderdaad overheen gelezen.