How To Group By Last Date

fa

fasenderos 30 Sep, 2016

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

fa

fasenderos 01 Oct, 2016

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.

Max_admin 23 Jan, 2017

Hi fasenderos,

This is now possible in Connectivity v6 if you use a hasMany or subQuery join relationship between your tables.

Best regards,
Max

Max
ChronoForms developer...
Did you try ChronoMyAdmin for managing your Joomla database tables ?

Forums

How To Group By Last Date