TransWikia.com

How to get records ordered by joined table field

Stack Overflow Asked by Jaime on December 16, 2021

I have two tables:

chat:
id
name

chat_messages:
id
chat_id
user_id
message
created_at

I want to get a list of chats names ordered by recent activity. In other words, I want to get a list of chats where the first one is that with the bigger created_at field, and the last one is that with the smaller created_at field.

For example:

chat table:
1 General
2 News

chat_messages:
1 | 1 | 20 | Hello everybody | 2020-10 18:00:00
1 | 1 | 23 | this is a me... | 2020-10 18:00:05
1 | 1 | 15 | another message | 2020-10 18:00:15
1 | 2 | 22 | Anybody there?  | 2020-10 17:00:00
1 | 2 | 45 | Hello?????????? | 2020-10 16:00:00

The desired result would be: [‘News’, ‘General’]

Any help?
Thanks

2 Answers

Select last Created_at for every chat:

  SELECT m.chat_id,
         max(m.created_at) as LastActivity
    FROM chat_message as m
group by m.chat_id;

Let's find some infos about chats, by joining the chat table:

SELECT c.name,
       info.LastActivity
  FROM (  SELECT m.chat_id,
             max(m.created_at) as LastActivity
        FROM chat_message as m
    group by m.chat_id) info -- virtual table "info" from first query
  JOIN chat as c ON c.id = info.chat_id; -- add table chat to query

Now we want to add some info about the LastActivity

SELECT c.name,
       info.LastActivity,
       m2.user_id, -- Print Infos of LastActivityMessage
       m2.message
  FROM (  SELECT m.chat_id,
             max(m.created_at) as LastActivity
        FROM chat_message as m
    group by m.chat_id) info
  JOIN chat as c ON c.id = info.chat_id
  JOIN chat_message as m2 ON info.LastActivity = m2.created_at; -- Search for Message at LastActivity

In out last query we see a problem: We do not want to search for a messag with it's date! We're missing a propper primary-Key in table chat_message.

I'd suggest to calculate a unique id:

create table chat_message
(
    id int not null auto_increment primary key, -- this is the message-id which will automatically set
    chat_id int not null, -- where did the user post?
    user_id int not null, -- which user?
    message text,
    created_at datetime
);

If we insert our Messages now like this:

INSERT INTO chat_message (chat_id, user_id, message, created_at) 
  VALUES
  (2, 45, "Hello?", "2020-10-01 16:00:00"),
  (2 , 22 , "Anybody there?"  , "2020-10-01 17:00:00"),
  (1, 20, "Hello everybody", "2020-10-01 18:00:00"),
  (1, 23,  "this is a me...", "2020-10-01 18:00:05"),
  (1, 15, "another message", "2020-10-01 18:00:15");

..Ids are generated automatically:

id  chat_id user_id message         created_at
1   2       45      Hello?          2020-10-01T16:00:00Z
2   2       22      Anybody there?  2020-10-01T17:00:00Z
3   1       20      Hello everybody 2020-10-01T18:00:00Z
4   1       23      this is a me... 2020-10-01T18:00:05Z
5   1       15      another message 2020-10-01T18:00:15Z

This leads to a better query without searching for a single message with it's date:

-- Now we want to add some info about the LastActivity
SELECT c.name,
       m2.id,
       m2.created_at,
       m2.user_id,
       m2.message
  FROM (  SELECT m.chat_id,
             max(m.id) as LastActivity -- select last Id
        FROM chat_message as m
    group by m.chat_id) info
  JOIN chat as c ON c.id = info.chat_id
  JOIN chat_message as m2 ON info.LastActivity = m2.id; -- search for message with correct id

Answered by kara on December 16, 2021

Try this:

SELECT DISTINCT `chat`.`name`
FROM `chat` JOIN `chat_message` ON `chat`.`id` = `chat_message`.`chat_id`
ORDER BY `chat_message`.`created_at` DESC

Explanation:

You only want the chat name to be returned. And you also want each value only once (that's the DISTINCT).

You ORDER BY the created_at field, and then the only thing left to do is to JOIN the tables.

Edit: you can try / improve here.

Answered by noam on December 16, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP