I'm currently working on an e-commerce project and I would like to create a landing page that shows every product category with per category the 4 latest products. I'm using MySQL, plain query.
My database is fairly simple, there is a products table and a product_category table. Of course there's a link from the products table to the product_category table: product_category_id.
Edit: i have created a db fiddle: https://www.db-fiddle.com/f/fmy7mBfZ2vcu7hhumU59ou/0
Of course, the sorting is easy, but i need just 4 product results per category_id.
Above the product_category table there is a product_main_category table, but I don't think this is relevant in this example.
You can do this in mysql 5.7
SELECT
t.`id`
, t.`product_category_id`
,t.`name`, t.`slug`
, t.`description`
, t.`price`
, t.`created_at`
, t.`updated_at`
FROM
(
SELECT
`id`, `name`, `slug`, `description`, `price`, `created_at`, `updated_at`,
@row_num:=CASE WHEN @p_id = product_category_id THEN @row_num + 1 ELSE 1 END AS rn,
@p_id:=product_category_id as product_category_id
FROM
products,(SELECT @p_id := 0) a1,(SELECT @row_num := 0) a2
ORDER BY product_category_id, created_at
) t inner join product_categories pc on t.product_category_id = pc.id
WHERE t.rn <=4
ORDER BY t.product_category_id, t.id;
Which gives you following result
id | product_category_id | name | slug | description | price | created_at | updated_at -: | ------------------: | :------------------- | :-------------------------------------------------------------------- | :----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | -----: | :------------------ | :------------------ 17 | 1 | Sandra Thompson | dolores-quidem-vel-sit-consequatur | Porro dolorem voluptas sapiente at debitis et est. Sed molestias qui minima enim. Et nemo ad sed voluptate recusandae optio quaerat. Est occaecati eos non nostrum voluptatem porro. | 103.96 | 2020-03-12 23:38:50 | 2020-04-07 18:38:35 38 | 1 | Mr. Domenic Kozey | a-vero-quis-facilis | Reprehenderit fugit voluptatibus illo hic ut commodi. Deserunt maiores totam esse at voluptas et. Iusto et harum animi tenetur iste voluptatem. | 195.03 | 2020-03-09 04:10:12 | 2020-04-07 18:38:35 46 | 1 | Rosario Schmitt | eveniet-a-rerum-sapiente-iure-in-et | Nisi delectus labore autem qui dolores beatae accusamus. Libero totam consectetur aut rerum atque. | 126.25 | 2020-03-13 02:12:21 | 2020-04-07 18:38:35 48 | 1 | Kaela Lueilwitz V | ipsum-laborum-beatae-quia-repellat-hic-maxime | Magni non inventore quos. Et quasi itaque id. Corrupti sed maiores rem in. | 43.01 | 2020-03-11 00:57:50 | 2020-04-07 18:38:35 6 | 2 | Jeremie Schneider | sit-autem-molestiae-eveniet | Corporis quidem aut nostrum suscipit in minima dicta autem. Nostrum porro dicta quo ut vero inventore enim. Repudiandae architecto voluptatem temporibus illo eum. | 3.77 | 2020-03-11 08:06:41 | 2020-04-07 18:38:35 27 | 2 | Devan Hickle | eaque-explicabo-aliquid-architecto-rem-odit-atque | Molestiae molestias eos voluptatem vero sunt id voluptatem neque. Molestiae nam cum aliquid. Odio non ab et non incidunt sed. | 60.81 | 2020-03-09 05:34:30 | 2020-04-07 18:38:35 32 | 2 | Prof. Liana Torp | quasi-eaque-soluta-sed-voluptates-odio | Voluptas est saepe voluptatem facere voluptates ratione. Esse rerum nihil deleniti nihil. Qui ut eum velit aut. Culpa rerum iure neque deserunt et. | 67.77 | 2020-03-16 00:27:40 | 2020-04-07 18:38:35 33 | 2 | Abdiel Schowalter | quam-sapiente-omnis-aut-et-rerum-perferendis | Fuga dolor dolore et in rerum at. Voluptatibus quaerat ab eum qui necessitatibus maiores et. Quam consequatur ea quia maiores qui consequatur reiciendis. | 3.53 | 2020-03-08 01:00:48 | 2020-04-07 18:38:35 20 | 3 | Mr. Eleazar Mante IV | voluptatem-consequatur-consequuntur-qui-veritatis-provident-dolor-quo | Illum sed eaque corrupti fuga. Sunt deserunt et iusto possimus id hic totam. Et voluptatem tempora nihil consequuntur voluptatem sequi quasi. | 91.52 | 2020-03-08 07:44:49 | 2020-04-07 18:38:35 23 | 3 | Fredy Morar | error-aut-excepturi-fuga-aperiam-sunt-illum-ea | Libero non numquam optio. Nam aut reiciendis necessitatibus voluptates. Molestias officia in est tenetur. | 181.54 | 2020-03-30 05:25:37 | 2020-04-07 18:38:35 36 | 3 | Albina Hansen | nam-et-labore-ipsum-quidem-aut | Quo sequi consectetur quo amet quos. Eaque et velit aut quasi. Dolores incidunt autem blanditiis officiis dolor accusamus. | 97.39 | 2020-03-26 03:35:56 | 2020-04-07 18:38:35 44 | 3 | Kenyon Mertz | ut-aliquam-aut-modi-sit-sunt-sint-dignissimos | Dolore in corrupti sit et quis aperiam quia. Aspernatur aspernatur consequuntur voluptatem repellat rerum. Minima aliquam dolorem recusandae numquam voluptates. | 47.23 | 2020-03-21 10:29:19 | 2020-04-07 18:38:35
db<>fiddle here
The result is ordered so that you can see there are 4 rows per category.
So this order and the inner selct should be check or adapted