get 2 record from each category doctrine query builder

Question

Lets say I have the following table

-----------------------------
| user_id   | comment       |
-----------------------------
| 2         | thats cool    |
| 2         | awesome       |
| 3         | i hate this   |
| 3         | okay          |
| 6         | this is weird |
| 6         | hello?        |
| 6         | what is it    |
| 9         | how are you   |
| 16        | too slow      |
| 16        | yes           |
| 17        | alrighty      |
-----------------------------

How can you select two rows per user_id? So my results would be:

-----------------------------
| user_id   | comment       |
-----------------------------
| 2         | awsome        |
| 2         | thats cool    |
| 3         | i hate this   |
| 3         | okey          |
| 6         | this is weird |
| 6         | hello?        |
| 9         | how are you   |
| 16        | too slow      |
| 16        | yes           |
| 17        | alrighty      |
-----------------------------

Is this possible with a single efficient query? Or are sub-selected necessary?

This is a top N per group problem, which is hard to solve in MySQL. — Tim Biegeleisen, Apr 05 '18 at 08:42
See here: https://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results — Tim Biegeleisen, Apr 05 '18 at 08:43

Jay Shankar Gupta · Answer 1 · 2018-04-05T08:48:40.300

0

SELECT user_id,
       comment
FROM
(
SELECT user_id,
       comment,
       CASE WHEN @user_id = T.user_id THEN 
       @ROW:=@ROW+1
       ELSE
       @ROW:=1
       END ROW,
       @user_id:=T.user_id
FROM 
Table1 T
,(SELECT @ROW=1,@user_id=null) R)
AS T1 WHERE ROW <=2

OUTPUT

user_id comment
2       thats cool
2       awesome
3       i hate this
3       okay
6       this is weird
6       hello?
9       how are you
16      too slow
16      yes
17      alrighty

Live Demo

http://sqlfiddle.com/#!9/c31356/4

edited Apr 05 '18 at 08:48

answered Apr 05 '18 at 08:47

Jay Shankar Gupta

5,918
1
10
27

how can i user this query in doctrine query builder ? – M Maavia Apr 05 '18 at 08:48
The trick here is how to do this from Doctrine PHP, not raw MySQL. I'm not sure it would be possible to run your query in Doctrine. – Tim Biegeleisen Apr 05 '18 at 08:54

get 2 record from each category doctrine query builder

1 Answers1