Fetching N records excluding joined ones?

Question

I have three tables (details omitted for brevity):

create table products (
    id,
    name 
)

create table tags (
    id,
    name
)

create table product_tags (
    product_id,
    tag_id
)

These tables are populated as follows:

--------
products
--------
+----+------+
| id | name |
+----+------+
| 1  | Rice |
| 2  | Bean |
| 3  | Milk |
+----+------+

----
tags
----
+----+-------+
| id | name  |
+----+-------+
| 1  | Eat   |
| 2  | Drink |
| 3  | Seeds |
| 4  | Cow   |
+----+-------+

When fetching products I want the output to be formatted as:

[{
    id: 1,
    name: 'Rice',
    tags: [
        {
            id: 1,
            name: 'Eat'
        },
        {
            id: 3,
            name: 'Seeds'
        },
    ]
},
{
    id: 2,
    name: 'Bean',
    tags: [
        {
            id: 1,
            name: 'Eat'
        },
        {
            id: 3,
            name: 'Seeds'
        },
    ]
},
{
    id: 3,
    name: 'Milk',
    tags: [
        {
            id: 2,
            name: 'Drink'
        },
        {
            id: 4,
            name: 'Cow'
        },
    ]
}]

To accomplish this, what I'm doing is:

select 
  products.*,
  tags.id as tag_id, tags.name as tag_name
from products
left join product_tags map on map.product_id = products.id
left join tags on map.tag_id = tags.id

The output of which is:

[{
    id: 1,
    name: 'Rice',
    tag_id: 1,
    tag_name: 'Eat',
},{
    id: 1,
    name: 'Rice',
    tag_id: 3,
    tag_name: 'Seeds',
},{
    id: 2,
    name: 'Bean',
    tag_id: 1,
    tag_name: 'Eat',
},{
    id: 2,
    name: 'Bean',
    tag_id: 3,
    tag_name: 'Seeds',
},{
    id: 3,
    name: 'Milk',
    tag_id: 2,
    tag_name: 'Drink',
},{
    id: 3,
    name: 'Milk',
    tag_id: 4,
    tag_name: 'Cow',
}]

Which I parse by hand and aggregate each product instance with an array of zero or more tag objects that it is associated with.

Question

When doing the select above, the output is 6 rows. However, there are only 3 products. Is it possible to use a limit clause that would apply to just the products?

For example, if Product.id => 1 had 10 tags associated with it, doing a LIMIT 5 would only select 5 of the tags. What I want to do is select 5 products along with all tags associated with it.

The only way I can think of achieving this is to select just the products, then do an unbounded SELECT with just the product IDs from the previous query.

Bonus question

Is there a more efficient way to do this JOIN such that the output is aggregated as above?

The only results in SQL are 2-dimensional tables. If you want to reorganize the results into more complex data you have to do it in an external programming language like PHP or Python. — Barmar, Apr 21 '18 at 03:41
See https://stackoverflow.com/questions/24302630/how-to-combine-an-array-in-javascript/24302781#24302781 for how to do it in Javascript, https://stackoverflow.com/questions/35473174/creating-one-array-from-another-array-in-php for PHP. — Barmar, Apr 21 '18 at 03:43

score 1 · Accepted Answer · answered Apr 21 '18 at 03:47

Use a subquery to select 5 products, then join with tags:

SELECT p.name as product, t.name AS tag
FROM (
    SELECT id, name
    FROM products
    ORDER by name
    LIMIT 5) AS p
JOIN product_tags AS pt ON pt.product_id = p.id
JOIN tags AS t ON pt.tag_id = t.id

Fetching N records excluding joined ones?

Question

Bonus question

1 Answers1