How To Group By Last Date

Question

Hi,I have two models called ARTICLE and MESSAGE with HASMANY relation, and I want to retrieve articles ordered by last message, to show Article Title and Last Message for ex:- Article Title (ID 20)   last message text create 1 HOUR AGO  - Article Title (ID 10)   last message text create 1 DAY AGO  - Article Title (ID 30)   last message text create 1 MONTH AGOThese are the tables:ARTICLE: ID | USER_ID | TITLE | CONTENT | LAST_MESSAGE_DATE ===>     "last_message_date" & "created" MESSAGE: ID | USER_ID | ARTICLE_ID | TEXT | CREATED ============>         have THE SAME VALUEMESSAGE model has ARTICLE_ID as a FOREIGN KEYI was able to order the articles by inserting LAST_MESSAGE_DATE DESC in the ORDER field of the ARTICLE model, but I was not able to get the last message.I tried to insert CREATED DESC in the ORDER field of the MESSAGE model, but the query returns ALL messages ordered by date, insted i would like to get only the last message for each article.Than I tried to group by inserting ARTICLE_ID in the GROUP field without success. It seems that always return the first message not the last, even if I set order by CREATED DESC.I know that to obtain something like this in MYSQL i need to use MAX(CREATED), so I try to insert in the FIELDS field of the MESSAGE model: ID, USER_ID, ARTICLE_ID, TEXT, MAX(CREATED), but what i got is a strange result: ID, USER_ID, ARTCILE_ID and TEXT of the FIRST message, while the CREATED date of the LAST message.Thanks in Advance

fasenderos · Accepted Answer

SELECT * FROM article art 	INNER JOIN (         SELECT message.*          FROM message         INNER JOIN (                    SELECT user_id,article_id,MAX(created) as lastmessage             FROM message             GROUP BY user_id,article_id         ) as t1         ON t1.user_id = message.user_id          AND t1.article_id = message.article_id          AND t1.lastmessage = message.created     ) msg      ON art.id = msg.article_id ORDER BY msg.created DESC This is the right mysql query, now I need to "translate it" in the chronoconnectivity language🙂Any help appreciated.

Forums

How To Group By Last Date