Mysql: select the last 10 messages and for each message the last 3 replies

Question

For simplicity, allows you to hide the message table to a minimum, with some sample data

message_id  reply_to    createdate
1           0           123
2           0           124
3           0           123
4           1           154
5           1           165

reply_to - message_id whose message is a response to

so I'm looking for a sql-statement / procedure / function / other table construct that allows me to select the last 10 messages, and the last 3 answers for each of them, I don’t mind changing the structure of the table or even keeping some record for the last 3 answers

just pick the last 10 posts

SELECT * FROM message ORDER BY createdate LIMIT 10;

and for each of these messages the answers

SELECT * FROM message WHERE reply_to = :message_id: ORDER BY createdate LIMIT 3;

my attempts so far:

• triple outer join above the message table as responses
• simple connection but mysql does not allow connection restrictions
• HAVING COUNT (DISTINCT reply_to) <= 3, , , HAVING

,

atm 3

message_reply: message_id, r_1, r_2, r_3

, , , message_reply

UPDATE message_reply SET r_3 = r_2, r_2 = r_1, r_1 = NEW.reply_to WHERE message_id = NEW.message_id

SQL?

EDIT:

EXPLAIN

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   PRIMARY     <derived4>  ALL     NULL    NULL    NULL    NULL    3    
1   PRIMARY     <derived2>  ALL     NULL    NULL    NULL    NULL    10  Using where; Using join buffer
1   PRIMARY     r   eq_ref  PRIMARY,message_id,message_id_2     PRIMARY     4   func    1    
4   DERIVED     NULL    NULL    NULL    NULL    NULL    NULL    NULL    No tables used
5   UNION   NULL    NULL    NULL    NULL    NULL    NULL    NULL    No tables used
6   UNION   NULL    NULL    NULL    NULL    NULL    NULL    NULL    No tables used
NULL    UNION RESULT    <union4,5,6>    ALL     NULL    NULL    NULL    NULL    NULL     
2   DERIVED     m   ALL     NULL    NULL    NULL    NULL    299727   
3   DEPENDENT SUBQUERY  r   ref     reply_to,reply_to_2     reply_to_2  4   testv4.m.message_id     29973    

2:

, table_reply table , ,

:

message_reply: message_id, r_1, r_2, r_3

:

DELIMITER |
CREATE TRIGGER i_message AFTER INSERT ON message
  FOR EACH ROW BEGIN
    IF NEW.reply_to THEN
        INSERT INTO message_replies (message_id, r_1) VALUES (NEW.reply_to, NEW.message_id)
        ON DUPLICATE KEY UPDATE r_3 = r_2, r_2 = r_1, r_1 = NEW.message_id;
    ELSE
        INSERT INTO message_replies (message_id) VALUES (NEW.message_id);
    END IF;
  END;
|
DELIMITER ;

:

SELECT m.*,r1.*,r2.*,r3.* FROM message_replies mr
LEFT JOIN message m ON m.message_id = mr.message_id
LEFT JOIN message r1 ON r1.message_id = mr.r_1
LEFT JOIN message r2 ON r2.message_id = mr.r_2
LEFT JOIN message r3 ON r3.message_id = mr.r_3

, .

100 ., 0,4 100 ., 12 ( myIsam)

Answer 1

:

EDIT - (. )

. "datetable" 10 . .

drop table if exists messages;
create table messages (
   message_id int primary key, reply_to int, createdate datetime, index(reply_to));

insert into messages 
select @n:=@n+1, floor((100000 - @n) / 10), a.thedate
from (select @n:=0) n
cross join datetable a
cross join datetable b
limit 1000000;

1 . :

select m1.message_id, m1.reply_to, m1.createdate, N.N, r.*
from
(
    select m.*, (
         select group_concat(r.message_id order by createdate)
          from messages r
        where r.reply_to = m.message_id) replies
     from messages m
     order by m.message_id
    limit 10
) m1
inner join ( # this union-all query controls how many replies per message
    select 1 N union all
     select 2 union all
     select 3) N
  on (m1.replies is null and N=1) or (N <= length(m1.replies)-length(replace(m1.replies,',','')))
left join messages r
  on r.message_id = substring_index(substring_index(m1.replies, ',', N), ',', -1)

: 0,078

id     select_type         table        type      possible_keys    key      key_len ref                rows    Extra
1      PRIMARY             <derived4>   ALL      (NULL)            (NULL)   (NULL)  (NULL)             3    
1      PRIMARY             <derived2>   ALL      (NULL)            (NULL)   (NULL)  (NULL)             10      Using where
1      PRIMARY             r            eq_ref   PRIMARY           PRIMARY  4       func               1    
4      DERIVED             (NULL)       (NULL)   (NULL)            (NULL)   (NULL)  (NULL)             (NULL)  No tables used
5      UNION               (NULL)       (NULL)   (NULL)            (NULL)   (NULL)  (NULL)             (NULL)  No tables used
6      UNION               (NULL)       (NULL)   (NULL)            (NULL)   (NULL)  (NULL)             (NULL)  No tables used
(NULL) UNION RESULT        <union4,5,6> ALL      (NULL)            (NULL)   (NULL)  (NULL)             (NULL)    
2      DERIVED             m            index    (NULL)            PRIMARY  4       (NULL)             1000301    
3      DEPENDENT SUBQUERY  r            ref      reply_to          reply_to 5       test.m.message_id  5       Using where

Answer 2

. . SQL .

Answer 3

.. OMG, , , .

OMG: mysql " n--" - . OMG: , .

OMG, , https://stackoverflow.com/questions/tagged/greatest-n-per-group+mysql

• SQL - 3
• mySQL
• MySQL SELECT n GROUP BY

, - 3, (2 ), ( ) . , , row_number ALL.

top-n-category -> top-m-per-category, , .

visit the questions and courteously inform if not answer,

• .