Given these two tables
lists
id | name | weight |
---|---|---|
1 | Groceries | 0 |
2 | Pharmacy | 1 |
3 | Misc | 2 |
list_items
id | list | weight |
---|---|---|
1 | 1 | 1 |
2 | 1 | 0 |
3 | 1 | 2 |
4 | 2 | 1 |
5 | 2 | 0 |
6 | 1 | 3 |
Can I construct a query that returns:
id | name | items | totalNumberOfItems |
---|---|---|---|
1 | Groceries | 2, 1, 3 | 4 |
2 | Pharmacy | 5, 4 | 2 |
3 | Misc | null | 0 |
Ie. all of the lists — regardless of whether they have items or not — sorted by weight
, with the firs three items of each list (also sorted by the item's weight
) concatenated and comma separated.
This version adds the count, but I assume that if I can get a limit on the join to work that count will be capped at that limit: