-1

i have a comments table, like this:
id, answer_id, content

now i want to get latest 10 comments for EACH answer id.
list of answer ids are provided like (1,5,11,27,82)

is it possible to get all related comments by just one query ?

i currently do this by php's foreach:

foreach ($answers as $answer) { // query mysql for its comments
  • 1
    If you want the "latest" comments, you're going to need a temporal column, right? Or do you have some other way in mind? – KXNV-89.1FM Jun 19 '17 at 18:13
2

I found this answer helpful "Get top n records for each group of grouped results", and you should refer to it for an explanation of the query shown below.

DB-Fiddle

create table comments (
    id int not null, 
    answer_id int not null, 
    content varchar(100),
    dt datetime);

Populate with some data:

INSERT INTO comments VALUES (1,1,'numerable','2016-09-24 22:55:55'),(2,1,'reviver','2016-09-21 10:27:20'),(3,1,'statutory','2016-09-25 12:27:06'),(4,1,'lithograph','2016-09-23 14:35:29'),(5,1,'optima','2016-09-21 21:53:24'),(6,1,'rationales','2016-09-24 23:52:35'),(7,1,'directly','2016-09-27 15:51:09'),(8,1,'astounding','2016-09-22 08:44:40'),(9,1,'abut','2016-09-26 21:01:11'),(10,1,'synchronously','2016-09-24 19:53:08'),(11,1,'Dante','2016-09-26 16:10:29'),(12,1,'handcuff','2016-09-24 15:05:00'),(13,1,'apparition','2016-09-25 02:56:07'),(14,1,'vents','2016-09-27 18:17:58'),(15,1,'unduly','2016-09-22 14:14:24'),(16,1,'Swarthmore','2016-09-22 01:48:27'),(17,1,'expanding','2016-09-21 11:42:46'),(18,1,'shapelessness','2016-09-27 14:59:50'),(19,1,'differentiations','2016-09-24 04:14:17'),(20,1,'Geminid','2016-09-22 01:10:45');

INSERT INTO comments VALUES (21,5,'coordinators','2017-01-03 23:10:11'),(22,5,'fiftieth','2017-01-01 12:46:11'),(23,5,'heaving','2017-01-11 18:57:39'),(24,5,'computer','2017-01-11 20:28:05'),(25,5,'instill','2017-01-07 18:23:06'),(26,5,'Steiner','2017-01-03 05:00:50'),(27,5,'decompression','2017-01-06 13:05:00'),(28,5,'rainstorm','2017-01-03 11:16:04'),(29,5,'gossiped','2017-01-08 22:02:20'),(30,5,'fledged','2017-01-01 19:32:23'),(31,5,'overlapping','2017-01-06 19:36:01'),(32,5,'orations','2017-01-06 00:04:57'),(33,5,'interprocess','2017-01-04 11:33:25'),(34,5,'investigates','2017-01-10 12:54:02'),(35,5,'hamlet','2017-01-02 11:58:37'),(36,5,'clapping','2017-01-06 17:21:52'),(37,5,'breakpoints','2017-01-08 03:03:55'),(38,5,'caviar','2017-01-07 03:24:26'),(39,5,'searched','2017-01-07 03:05:39'),(40,5,'unselfishly','2017-01-07 15:34:07');

Suggested query:

SELECT id, answer_id, content, dt
FROM
(
    SELECT
        id,
        answer_id,
        content,
        dt,
    @rn := IF(@prev = answer_id, @rn + 1, 1) AS rn,
    @prev := answer_id
    FROM comments
    JOIN (SELECT @prev := NULL, @rn := 0) AS vars
    ORDER BY answer_id, dt DESC
) AS T1
WHERE rn <= 10 AND answer_id IN (1,5);

Results:

id    answer_id    content        dt
14    1            vents          2016-09-27 18:17:58
 7    1            directly       2016-09-27 15:51:09
18    1            shapelessness  2016-09-27 14:59:50
 9    1            abut           2016-09-26 21:01:11
11    1            Dante          2016-09-26 16:10:29
 3    1            statutory      2016-09-25 12:27:06
13    1            apparition     2016-09-25 02:56:07
 6    1            rationales     2016-09-24 23:52:35
 1    1            numerable      2016-09-24 22:55:55
10    1            synchronously  2016-09-24 19:53:08
24    5            computer       2017-01-11 20:28:05
23    5            heaving        2017-01-11 18:57:39
34    5            investigates   2017-01-10 12:54:02
29    5            gossiped       2017-01-08 22:02:20
37    5            breakpoints    2017-01-08 03:03:55
25    5            instill        2017-01-07 18:23:06
40    5            unselfishly    2017-01-07 15:34:07
38    5            caviar         2017-01-07 03:24:26
39    5            searched       2017-01-07 03:05:39
31    5            overlapping    2017-01-06 19:36:01
  • @MehdiAzmoudeh I don't know which is faster; sorry. And, yes, it is complicated -- even to me. – KXNV-89.1FM Jun 19 '17 at 22:00
1

You can generate a query with multiple SELECTs and combine the result with UNION:

SELECT * FROM comments WHERE answer_id = 1 ORDER BY id DESC LIMIT 10 
UNION
SELECT * FROM comments WHERE answer_id = 5 ORDER BY id DESC LIMIT 10
UNION
-- … and so on for 11, 27, 82
;

This sorts the query for each answer_id by id in descending order and gets the first 10 rows of the result, i.e. the last 10 rows for this answer_id.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.