The product has two colors (Black and White)
I would like to use a LIMIT to not bring all the colors... and the amount of colors would be the total
Example:
LIMIT 1 would return:
LIMIT 2 would return:
I have to return the names, inside a JSON
But I saw that there is no way to use LIMIT in JSON_ARRAYAGG
The solution would be to use a subquery in FROM
It worked... and I had to add the GROUP BY v.id
BUT the amount of colors ( amountColors ) is always 1 It should be 2 in the above two cases
WITH cte AS (
SELECT
pdv.variation_id
FROM product_detail pd
INNER JOIN product_detail_variation pdv
ON (pdv.product_detail_id = pd.id)
WHERE pd.product_id = -- ID-HERE
)
SELECT
JSON_ARRAYAGG(colorsName) AS colorsName,
amountColors
FROM (
SELECT
JSON_OBJECT(
'color', v.name
) AS colorsName,
COUNT(v.id) AS amountColors
FROM variation v
INNER JOIN grid g
ON (g.id = v.grid_id)
WHERE g.name = 'Color' AND EXISTS (TABLE cte)
GROUP BY v.id
LIMIT 1
) v
/* Test: LIMIT 1
array(2) {
["colorsName"]=>
string(20) "[{"color": "Black"}]"
["amountColors"] => int(1) // should return int(2)
}*/
/* Test: LIMIT 2
array(2) {
["colorsName"]=>
string(40) "[{"color": "Black"}, {"color": "White"}]"
["amountColors"] => int(1) // should return int(2)
}
*/
MySQL version: 8.0+
Look at this query:
WITH cte AS (
SELECT p.id p_id, p.name p_name,
pd.id pd_id, pd.price pd_price, pd.stock pd_stock,
pdv.id pdv_id,
v.id v_id, v.name v_name,
g.id g_id, g.name g_name,
COUNT(*) OVER (PARTITION BY pd.product_id) v_count,
ROW_NUMBER() OVER (PARTITION BY pd.product_id ORDER BY v.id) v_rn,
JSON_OBJECT( 'color', v.name ) AS ColorName
FROM product p
JOIN product_detail pd ON p.id = pd.product_id
JOIN product_detail_variation pdv ON pd.id = pdv.product_detail_id
JOIN variation v ON v.id = pdv.variation_id
JOIN grid g ON v.grid_id = g.id
WHERE g.name = 'Color'
AND pd.product_id = 1
)
SELECT p_name ProductName,
JSON_ARRAYAGG(ColorName) AS ColorNames,
v_count ColorsAmount
FROM cte
WHERE v_rn <= @colors_limit
GROUP BY 1, 3
Check it. The logic of the task is not completely clear for me now, I may be wrong...
And I recommend you to expand source data (add rows) and check again (with and without the condition by the product).