CODEWITHSUNDEEP
phpmysqldoctrine-ormsymfony
Jason Stephenson
Jason Stephenson

Reputation: 220

Left Join with pagination (LIMIT)

Imagine I have a table like this with a many to one relationship

Table1

  id | name
   1 | as
   2 | df
   3 | gh

and

  id | othercontents | table1relationship
   1 | qw            | 1
   2 | er            | 2
   3 | ty            | 3
   4 | ui            | 3

if I run a select query on Table1 with a left join for Table2 but limit it to 3 results I will get 3 rows returned

1 - as - qw
2 - df - er
3 - gh - ty

however I want

1 - as - qw
2 - df - er
3 - gh - [ty, ui]

Now, currently I am selecting it as normal and then putting othercontents into an array myself to turn my rows into how I want them, but the problem remains that I can not return all the rows I want.

Logically, I suppose I want to limit to X unique table1.id's rather than limiting to just X rows, but I do not know a way to implement this logic if it is even possible.

Ofcourse this is easy if I select everything in the database and then sort it in PHP but this is too intensive and I don't want to select 20,000 rows just to get ~10 rows. I suppose a hacky way around this would be to select 30 rows and then do my own sorting and return the 10 as I want them but it still seems silly to me to select more than I need.

Perhaps worth mentioning but I am using Symfony3 w/ Doctrine and using query builder. But I am not asking for copy/paste answer to my problem rather just a push in the direction so I can work on my implementation.

Thanks

Upvotes: 1

Views: 517

Answers (1)

Bernd Buffen
Bernd Buffen

Reputation: 15057

Here is a query that will give you the result ( if i understand you correct).

SELECT t1.id,t1.name,
    CONCAT( IF(sum(1)>1,'[',''), GROUP_CONCAT(t2.othercontents), IF(sum(1)>1,']','')) AS name2
FROM (
    SELECT *
    FROM table1 
    LIMIT 3
    ) as t1
LEFT JOIN table2 t2 on t2.table1relationship = t1.id
GROUP BY t2.table1relationship;

sample

mysql> SELECT * from table1;
+----+------+
| id | name |
+----+------+
|  1 | as   |
|  2 | df   |
|  3 | gh   |
+----+------+
3 rows in set (0,00 sec)

mysql> SELECT * from table2;
+----+---------------+--------------------+
| id | othercontents | table1relationship |
+----+---------------+--------------------+
|  1 | qw            |                  1 |
|  2 | er            |                  2 |
|  3 | ty            |                  3 |
|  4 | ui            |                  3 |
+----+---------------+--------------------+
4 rows in set (0,00 sec)

result

mysql> SELECT t1.id,t1.name,
    ->     CONCAT( IF(sum(1)>1,'[',''), GROUP_CONCAT(t2.othercontents), IF(sum(1)>1,']','')) AS name2
    -> FROM (
    ->     SELECT *
    ->     FROM table1
    ->     LIMIT 3
    ->     ) as t1
    -> LEFT JOIN table2 t2 on t2.table1relationship = t1.id
    -> GROUP BY t2.table1relationship;
+----+------+---------+
| id | name | name2   |
+----+------+---------+
|  1 | as   | qw      |
|  2 | df   | er      |
|  3 | gh   | [ui,ty] |
+----+------+---------+
3 rows in set (0,00 sec)

mysql>

Upvotes: 1

Related Questions