I make comments, they should be tree-like and they have only one degree of nesting (comment. Comments on YouTube).

Table structure

CREATE TABLE `_fx_comments` ( `id` int(11) NOT NULL AUTO_INCREMENT, `pid` int(11) DEFAULT NULL, `sender` int(11) NOT NULL, `recipient` int(11) DEFAULT NULL, `target` varchar(150) NOT NULL, `eid` int(11) NOT NULL, `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `text` text, `rateTrue` int(7) NOT NULL DEFAULT '0', `rateFalse` int(7) NOT NULL DEFAULT '0', `resource` text, `approve` int(1) NOT NULL DEFAULT '1', `isDel` int(1) NOT NULL DEFAULT '1', PRIMARY KEY (`id`), KEY `pid` (`pid`), KEY `sender` (`sender`), KEY `recipient` (`recipient`), KEY `target` (`target`), KEY `eid` (`eid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

eid - id of the element to which the comments belong, pid - id of the parent comment

The main task that I need to solve is to extract the last 10 comments from which the pid IS NOT NULL , and somehow look if the current comment has children, then extract them (two children are the last ones added).

But now I’ll somehow need to extract the last two children ..... There is an idea to use NESTED SET, but I don’t know how to use it in my example.

UPDATE :

At the moment there is such a terrible solution to the problem:

 SELECT fx_cmt.id,fx_cmt.pid,fx_cmt.eid,fx_cmt.sender,fx_cmt.recipient,fx_cmt.date,fx_cmt.text,fx_cmt.rateTrue,fx_cmt.rateFalse,fx_cmt.resource, fx_u.fx_login AS senderLogin, fx_u2.fx_login AS recipientLogin, (SELECT COUNT(fx_cmt_count.id) FROM _fx_comments fx_cmt_count WHERE fx_cmt_count.pid = fx_cmt.id) AS countChilds, fx_cmt_l.id AS lcId, fx_cmt_l.pid AS lcPid, fx_cmt_l.eid AS lcEid, fx_cmt_l.sender AS lcSender, fx_cmt_l.recipient AS lcRecipient, fx_cmt_l.date AS lcDate, fx_cmt_l.text AS lcText, fx_cmt_l.rateTrue AS lcRateTrue, fx_cmt_l.rateFalse AS lcRateFalse, fx_cmt_l.resource AS lcResource, fx_cmt_pl.id AS plcId, fx_cmt_pl.pid AS plcPid, fx_cmt_pl.eid AS plcEid, fx_cmt_pl.sender AS plcSender, fx_cmt_pl.recipient AS plcRecipient, fx_cmt_pl.date AS plcDate, fx_cmt_pl.text AS plcText, fx_cmt_pl.rateTrue AS plcRateTrue, fx_cmt_pl.rateFalse AS plcRateFalse, fx_cmt_pl.resource AS plcResource, fx_u_l.fx_login AS lcSenderLogin, fx_u2_l.fx_login AS lcRecipientLogin, fx_u_pl.fx_login AS plcSenderLogin, fx_u2_pl.fx_login AS plcRecipientLogin FROM _fx_comments AS fx_cmt INNER JOIN _fx_users fx_u ON fx_u.id = fx_cmt.sender LEFT JOIN _fx_users fx_u2 ON fx_u2.id = fx_cmt.recipient LEFT JOIN _fx_comments fx_cmt_l ON fx_cmt_l.id = (SELECT id FROM _fx_comments AS c WHERE c.pid = fx_cmt.id ORDER BY date DESC LIMIT 0,1) LEFT JOIN _fx_comments fx_cmt_pl ON fx_cmt_pl.id = (SELECT id FROM _fx_comments AS c WHERE c.pid = fx_cmt.id ORDER BY date DESC LIMIT 1,1) LEFT JOIN _fx_users fx_u_l ON fx_u_l.id = fx_cmt_l.sender LEFT JOIN _fx_users fx_u2_l ON fx_u2_l.id = fx_cmt_l.recipient LEFT JOIN _fx_users fx_u_pl ON fx_u_pl.id = fx_cmt_pl.sender LEFT JOIN _fx_users fx_u2_pl ON fx_u2_pl.id = fx_cmt_pl.recipient WHERE fx_cmt.eid = 1

But even though this option works, it is terrible, so a more correct solution is needed ....

    2 answers 2

    You can portray something like this:

     select * from ( select if(N=1, id, if(N=2, l_id, ll_id)) id, id as P_ID, N from (select id, (select id from _fx_comments L where L.pid=C.id order by date desc limit 0,1) as l_id, (select id from _fx_comments L where L.pid=C.id order by date desc limit 1,1) as ll_id from _fx_comments C where pid is null order by date desc limit 10) A, (select 1 as N union select 2 union select 3) B ) A inner join _fx_comments fx_cmt on fx_cmt.id=A.id left join _fx_users fx_u ON fx_u.id = fx_cmt.sender left join _fx_users fx_u2 ON fx_u2.id = fx_cmt.recipient order by P_ID, N

    The task of the main subquery is to get the ID of the last 10 kaments and next to them the ID of the last and the penultimate child of the kament. After that, it is multiplied by the query issuing the numbers 1, 2, 3. Next, IF expands all the received IDs vertically, in one column. To this, you can already glue basic information from kamentov and other tables. For the convenience of sorting the final output from the reference subquery, you can return any column required for sorting (in the example P_ID) plus the number N, according to which the final order will understand what is a first-level client and what is its child.

    • He swears at N in the seventh line You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'N from (select id, (select id from _fx_commen' at line 7 */ If you’re not in brackets, check that you’re not in brackets. but no. So far it looks like something very confusing for my perception) - sanu0074
    • one
      @ sanu0074 I forgot the comma after P_ID. I roughly sketched, I do not have your database for verification. trained on his own little plate. - Mike
    • this is my cant, I did not finish. I can misunderstand something, you can explain to me. Here is the table: hkar.ru/KdSK, but the current result of the sample is hkar.ru/KdSP It turns out that the children must select records with id 7.8 for pid = 5 and 13, 4 for pid = 3. But the sample looks strange, all N = 1 and all have pid. And I do not understand how to still distinguish the child records from the root. - sanu0074
    • one
      @ sanu0074 Your initial condition in question (pid is not null) is weird. The root pid entries must be NULL. So the main query should choose only them for good. Then they will have children too - Mike
    • one
      @ sanu0074 Do you have these records once selected by the main query (N = 1) and the second time as children (N> 1). Add the condition pid is null to the main query so that it takes only root records - Mike

    I recommend such a couple of links on the subject of your question:

    The data structure in the database is selected correctly: AL is best suited.

    In general, since you always have two recent comments: use the mysql feature like limit 0, 1 and limit 1.1 to get your comments and don’t worry about switching to NS.

    More specifically. Use two queries. Choose child comments sorted by date. In the first query, the limit is 0.1 (last comment), in the second query, the limit is 1.1 (the penultimate comment. Satisfied?

     SELECT `c1`.`id`, `c1`.`pid`, (SELECT id FROM `_fx_comments` AS `c` WHERE `c`.`pid` = `c1`.`id` ORDER BY `date` DESC LIMIT 0,1) AS last_comment, (SELECT id FROM `_fx_comments` AS `c` WHERE `c`.`pid` = `c1`.`id` ORDER BY `date` DESC LIMIT 1,1) AS prev_last_comment FROM `_fx_comments` AS `c1` WHERE 1 = 1

    Here you will have id records that you need (or NULL if there are no such records).

    Solution for your specific question. Is suitable?

    • Not really, so I get additional fields in the sample - hkar.ru/Kd4v , but I need full records with all the fields. those. How to get all the data about the comments on the received id? - sanu0074
    • do two joins? and then there will be many fields like prev_last_comment_sender, last_comment_sender, last_comment_date ... etc. This is an option, of course, but I don’t think that the best is sanu0074
    • It seems to me that you do not understand SQL well. This is not done in a single request. Try to open the Excel and jot down what you think the answer should look like, how you will separate the "real" lines of the table from the virtual last and virtual penultimate. - AK ♦
    • Yes, I am not very good at SQL, but it seemed to me that you can add rows with child comments to the sample without expanding it with columns like prev_last_comment_sender, last_comment_sender, last_comment_date ... and then sort the data into the array before output. Although maybe this is a bad option, I'm not sure - sanu0074
    • You have 13 columns (id, pid, sender ...). You want to add 13 more columns (last_comment_id, last_comment_pid, last_comment_sender ...) and 13 more columns for the previous comment. Not that it is completely impossible, but it is so bad practice, bl-rr-rr, that I do not recommend to consider it as a solution. - AK ♦