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:
These are the tables:
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
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 AGOThese 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 VALUEMESSAGE 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
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.
Hi fasenderos,
This is now possible in Connectivity v6 if you use a hasMany or subQuery join relationship between your tables.
Best regards,
Max
This is now possible in Connectivity v6 if you use a hasMany or subQuery join relationship between your tables.
Best regards,
Max
This topic is locked and no more replies can be posted.
