How to use LIMIT on LEFT JOIN?

Question

I have 2 tables called categories and products with this relationship:

categories.category_id = products.product_category_id

I want to show all categories with 2 products of them!

[
    [category 1] =>
            [
                'category_id' => 1,
                'category_title' => 'category_title 1',
                [products] => [
                    '0' => [
                            'product_category_id' => 1,
                            'product_id' => 51,
                            'product_title' => 'product_title 1',
                        ],
                    '1' => [
                            'product_category_id' => 1,
                            'product_id' => 55,
                            'product_title' => 'product_title 2',
                        ]
                ]
            ],
    [category 2] =>
            [
                'category_id' => 2,
                'category_title' => 'category_title 2',
                [products] => [
                    '0' => [
                            'product_category_id' => 2,
                            'product_id' => 32,
                            'product_title' => 'product_title 3',
                        ],
                    '1' => [
                            'product_category_id' => 2,
                            'product_id' => 33,
                            'product_title' => 'product_title 4',
                        ]
                ]
            ],
    ...
]

I Laravel eloquent I can use something like this:

$categories = Category::with(['products' => function($q) {
    $q->limit(2)
}])->get();

But I am not using Laravel and I need pure SQL code! I've tried this code:

SELECT
    CT.category_id,
    PT.product_category_id,
    CT.category_title,
    PT.product_id,
    PT.product_title
FROM
    categories CT
LEFT JOIN(
SELECT
    *
FROM
    products 
    LIMIT 2
) AS PT
ON
    CT.category_id = PT.product_category_id
WHERE
    CT.category_lang = 'en' 

But there is a problem with this code! It seems that MYSQL first gets 2 first rows in products table and then tries to have a LEFT JOIN from categories to that 2 rows! and this cause returning null values for products (while if I remove LIMIT it works great but I need LIMIT)

I've tested this code too: (UPDATED)

SELECT
    CT.category_id,
    PT.product_category_id,
    CT.category_title,
    PT.product_id,
    PT.product_title
FROM
    categories CT
LEFT JOIN(
    SELECT
        *
    FROM
        products
    WHERE
        product_category_id = CT.category_id
    LIMIT 5
) AS PT
ON
    CT.category_id = PT.product_category_id
WHERE
    CT.category_lang = 'en'

But I've received this error:

1054 - Unknown column 'CT.category_id' in 'where clause'

I cant access to CT.category_id in the subquery.

what is the best solution?

Answer 1

In MySQL, you would need to use variables. The basic idea is:

select p.*
from (select p.*,
             (@rn := if(@c = product_category_id, @rn + 1,
                        if(@c := product_category_id, 1, 1)
                       )
             ) as rn
      from (select p.* 
            from products p
            order by p.product_category_id
           ) p cross join
           (select @c := -1, @rn := 0) params
     ) p
where rn <= 2;

You can use this as your subquery and then join in the rest of the category information.

Here is a db<>fiddle if this code working.

In MySQL 8+/MariaDB 10.2+, this is much more easily written as:

select p.*
from (select p.*,
             row_number() over (partition by product_category_id order by product_category_id) as seqnum
      from products p
     ) p
where seqnum <= 2;

Note: You can specify which 2 products you want (say, the first two alphabetically) by adjusting the order by clauses in the two queries.

EDIT:

In MariaDB 10.2, you apparently cannot use a subquery for the ordering, so the appropriate query would be:

select p.*
from (select p.*,
             (@rn := if(@c = product_category_id, @rn + 1,
                        if(@c := product_category_id, 1, 1)
                       )
             ) as rn
      from products p cross join
           (select @c := -1, @rn := 0) params
      order by p.product_category_id
     ) p
where rn <= 2;

Here is the db<>fiddle for that.

You can also use this method:

select p.*
from products p
where (select count(*)
       from products p2
       where p2.product_category_id = p.product_category_id and p2.id <= p.id
      ) <= 2;

I don't really recommend this, because the correlated subquery typically has much worse performance than the order by. But if there are not too many products per category, then this will be fine (with the right indexes).