All geek questions in one place

Getting the last row in each group?

Hi, basically, I'm trying to copy a messaging system that has facebook on my site.

This is the logic ... "When user1 CREATES A NEW MESSAGE to send to user7, a new thread is created with thread_id from 1 (table: messages_thread), and a new record is inserted into the table: messages that are message_id 1 (table: messages). When user7 REPLYS to message user1, message2 is created, and it has thread_id of 1.

Now when user 1 CREATES a NEW MESSAGE to send to user7, thread 2 is created and message 3 is created. When user7 answers thread2, message 4 is created (I hope you get the logic.)

Everything is good. the only problem is that I need to select the newest message in the stream, but I am having problems with sql,

This sql that I have right now ...

SELECT max(message_id) message_id, m.thread_id, m.body, m.user_id,m.to_id, m.message_status, m.new, m.date, u.id, u.displayname, u.username, u.profile_img FROM messages m INNER JOIN users u ON u.id = m.user_id WHERE to_id = 7 AND (message_status = 'unread' or message_status='read' or message_status='saved') group by thread_id Order by message_id Desc LIMIT 10

Produces this ...

 +------------+-----------+----------------------+---------+-------+----------------+-----+------------+----+--------------+----------+-------------+ | message_id | thread_id | body | user_id | to_id | message_status | new | date | id | displayname | username | profile_img | +------------+-----------+----------------------+---------+-------+----------------+-----+------------+----+--------------+----------+-------------+ | 6 | 2 | Really nice | 1 | 7 | read | 0 | 1298617367 | 1 | Kenny Blake | imkenee | 28_1 | | 4 | 1 | Whats good with you? | 1 | 7 | read | 0 | 1298607438 | 1 | Kenny Blake | imkenee | 28_1 | +------------+-----------+----------------------+---------+-------+----------------+-----+------------+----+--------------+----------+-------------+

This is good, but one small problem, it selects the first row in each group, and im trying to select the newest (last row) in each group

How can i do this? here are the tables. Thanks!

Table: Messages_thread

 +----+---------+----------------+-------------+-----------+---------------+-------------+------------+ | id | user_id | subject | from_status | to_status | from_s_delete | to_s_delete | date | +----+---------+----------------+-------------+-----------+---------------+-------------+------------+ | 1 | 1 | Hey Kenny | unread | unread | 0 | 0 | 1298607438 | | 2 | 7 | Check out this | unread | unread | 0 | 0 | 1298617344 | +----+---------+----------------+-------------+-----------+---------------+-------------+------------+

Table Messages

 +------------+-----------+---------+-------+-----------------------------------------------------------+----------------+-----------------+-----+------------+ | message_id | thread_id | user_id | to_id | body | message_status | is_sent_deleted | new | date | +------------+-----------+---------+-------+-----------------------------------------------------------+----------------+-----------------+-----+------------+ | 1 | 1 | 1 | 7 | Whats good with you? | read | 0 | 0 | 1298607438 | | 2 | 1 | 7 | 1 | Nothing Kenny just chilling. Whats up with you though???? | read | 0 | 0 | 1298607473 | | 4 | 1 | 1 | 7 | Just posted victor how are you man? | read | 0 | 0 | 1298607956 | | 5 | 2 | 7 | 1 | Look at this poem.... | read | 0 | 0 | 1298617344 | | 6 | 2 | 1 | 7 | Really nice | read | 0 | 0 | 1298617367 | | 7 | 2 | 7 | 1 | Yea i know right :) | unread | 0 | 0 | 1298617383 | +------------+-----------+---------+-------+-----------------------------------------------------------+----------------+-----------------+-----+------------+
+4
source share
2 answers

Group the threads in a subquery that will return the last message for each thread:

 SELECT m.message_id, m.thread_id, m.body, m.user_id, m.to_id, m.message_status, m.new, m.date, u.id, u.displayname, u.username, u.profile_img FROM messages m INNER JOIN users u ON u.id = m.user_id INNER JOIN ( SELECT MAX(message_id) MaxMsgIDForThread FROM messages WHERE to_id = 7 AND (message_status = 'unread' or message_status='read' or message_status='saved') GROUP BY thread_id ) g ON m.message_id = g.MaxMsgIDForThread Order by m.message_id Desc LIMIT 10

It is possible that WHERE needs to be moved to an external request, now it will select the last message that matches the criteria, move it to an external request if you want to completely skip the stream if the conditions are not met.

You should also consider storing the message status as ENUM , which will help to compare.

+2
source

Unable to add order to group
But maybe this works:

 SELECT max(message_id) message_id, MAX(m.thread_id), m.body, m.user_id,m.to_id, m.message_status, m.new, m.date, u.id, u.displayname, u.username, u.profile_img FROM messages m INNER JOIN users u ON u.id = m.user_id WHERE to_id = 7 AND (message_status = 'unread' or message_status='read' or message_status='saved') group by thread_id Order by message_id Desc LIMIT 10
-1
source

Source: https://habr.com/ru/post/1341263/

More articles:


All Articles