many to many - How can I limit a subquery [MySQL] with union table

Question

I have following tables:

• product_category (union table) product_category_id (PK AI) product_id category_id

• categories category_id (PK AI) title ...etc

• products product_id (PK AI) title order etc...

I want get all categories with their associated products by union table (product_category) and limit their products to 5 per category, basically an many-to-many relationship, like netflix categories carousel (drama category have 5 associated movies and terror category have 5 associated movies etc...) I've tried this but join is not working as I expected:

SELECT 
*
FROM
product_category AS pc
    INNER JOIN
categories AS cat ON cat.category_id = pc.category_id
    INNER JOIN
(SELECT 
    *
FROM
    products
LIMIT 1 ORDER BY products.order DESC) AS x ON x.product_id = pc.product_id;

So... ?How can I limit products per category? i'am using MySQL 5.7 I dont want to query multiple times the database to get manually products by category, I want do the job by the cleanest way with only query or two at most. ?It is posible?

Regards.

question from:https://stackoverflow.com/questions/65895625/how-can-i-limit-a-subquery-mysql-with-union-table

Answer 1

On MySQL 5.7, you may try something along these lines, using an inner join to restrict to products with the most recent order value:

SELECT *
FROM product_category AS pc
INNER JOIN categories AS cat ON cat.category_id = pc.category_id
INNER JOIN products AS p ON p.product_id = pc.product_id
INNER JOIN
(
    SELECT product_id, MAX(`order`) AS latest_order
    FROM products
    GROUP BY product_id
) p2
    ON p2.product_id = p.product_id AND
       p2.latest_order = p.`order`;

The join to the subquery aliased as p2 above restricts to only product records having the latest order value. Side note: Please avoid naming your columns ORDER, which is a reserved MySQL keyword.

Edit: A solution for top 5 products per order, ordered by most recent first, using ROW_NUMBER:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY p.product_id ORDER BY `order` DESC) rn
    FROM product_category AS pc
    INNER JOIN categories AS cat ON cat.category_id = pc.category_id
    INNER JOIN products AS p ON p.product_id = pc.product_id
)

SELECT *
FROM cte
WHERE rn <= 5;