Création de combinaisons table / colonnes à l'aide de SQL Query ou Laravel SQL Query Builder
Nov 15 2020
J'ai un schéma existant pour les variations de produits.
Je souhaite créer une combinaison de chaque temps de production, quantités et options de variation.
Je créerai un formulaire de sélection en accédant aux quantités, délais de production, variations et options de variation du produit.
table_groups
+------------+
| id | title |
+----+-------+
| 1 | rug |
+----+-------+
table_days
+----+----------+------+
| id | group_id | day |
+----+----------+------+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
+----+----------+------+
table_quantities
+----+----------+-----------+
| id | group_id | quantity |
+----+----------+-----------+
| 1 | 1 | 100 |
| 2 | 1 | 200 |
| 3 | 1 | 300 |
| 4 | 1 | 400 |
+----+----------+-----------+
table_attributes
+----+----------+-----------+
| id | group_id | title |
+----+----------+-----------+
| 1 | 1 | Color |
| 2 | 1 | Size |
+----+----------+-----------+
table_attribute_values
+----+----------+--------------+--------+
| id | group_id | attribute_id | title |
+----+----------+--------------+--------+
| 1 | 1 | 1 | Red |
| 2 | 1 | 1 | Yellow |
| 3 | 1 | 1 | Black |
| 4 | 1 | 2 | Small |
| 5 | 1 | 2 | Medium |
+----+----------+--------------+--------+
J'ai préparé un exemple de schéma. Cependant, je n'obtiens pas le résultat que je souhaite.
Violon SQL
C'est ce que j'ai fait:
SELECT
GROUP_CONCAT(DISTINCT days_group) as days_list,
GROUP_CONCAT(DISTINCT quantities_group SEPARATOR ',') as quantities_list,
GROUP_CONCAT(DISTINCT attribute_values_group SEPARATOR ',') as attribute_values_list
FROM
table_groups
LEFT JOIN (
SELECT days.day, days.group_id,
GROUP_CONCAT(days.day) as days_group
FROM table_days days GROUP BY days.id
) joindays ON joindays.group_id = table_groups.id
LEFT JOIN (
SELECT quantities.quantity, quantities.group_id,
GROUP_CONCAT(quantities.quantity) as quantities_group
FROM table_quantities quantities GROUP BY quantities.id
) joinquantities ON joinquantities.group_id = table_groups.id
LEFT JOIN table_attributes attributes ON attributes.group_id = table_groups.id
LEFT JOIN (
SELECT attribute_id, group_id,
GROUP_CONCAT(attribute_values.title) as attribute_values_group
FROM table_attribute_values attribute_values
GROUP BY attribute_values.attribute_id, attribute_values.id
) joinattributevalues ON joinattributevalues.attribute_id = attributes.id
GROUP BY joinattributevalues.attribute_id;
Résultats de la requête:
+---------------+-----------+-----------------+-----------------------+
| group_id | days_list | quantities_list | attribute_values_list |
+---------------+-----------+-----------------+-----------------------+
| 1 | 1,2,3 | 100,200,300,400 | Red,Yellow,Black |
| 2 | 1,2,3 | 100,200,300,400 | Small,Medium |
+---------------+-----------+-----------------+-----------------------+
Le résultat correct que je veux devrait être le suivant. Pouvez-vous m'aider?
+-----------+---------------------+--------+
| group_id | combinations | price |
+-----------+---------------------+--------+
| 1 | 1-100-Red-Small | |
+-----------+---------------------+--------+
| 1 | 1-100-Red-Medium | |
+-----------+---------------------+--------+
| 1 | 1-100-Yellow-Small | |
+-----------+---------------------+--------+
| 1 | 1-100-Yellow-Medium | |
+-----------+---------------------+--------+
| 1 | 1-100-Black-Small | |
+-----------+---------------------+--------+
| 1 | 1-100-Black-Medium | |
+-----------+---------------------+--------+
| 1 | 1-200-Red-Small | |
+-----------+---------------------+--------+
| 1 | 1-200-Red-Medium | |
+-----------+---------------------+--------+
| 1 | 1-200-Yellow-Small | |
+-----------+---------------------+--------+
| 1 | 1-200-Yellow-Medium | |
+-----------+---------------------+--------+
| 1 | 1-200-Black-Small | |
+-----------+---------------------+--------+
| 1 | 1-200-Black-Medium | |
+-----------+---------------------+--------+
| 1 | 1-300-Red-Small | |
+-----------+---------------------+--------+
| 1 | 1-300-Red-Medium | |
+-----------+---------------------+--------+
| 1 | 1-300-Yellow-Small | |
+-----------+---------------------+--------+
| 1 | 1-300-Yellow-Medium | |
+-----------+---------------------+--------+
| 1 | 1-300-Black-Small | |
+-----------+---------------------+--------+
| 1 | 1-300-Black-Medium | |
+-----------+---------------------+--------+
| 1 | 1-400-Red-Small | |
+-----------+---------------------+--------+
| 1 | 1-400-Red-Medium | |
+-----------+---------------------+--------+
| 1 | 1-400-Yellow-Small | |
+-----------+---------------------+--------+
| 1 | 1-400-Yellow-Medium | |
+-----------+---------------------+--------+
| 1 | 1-400-Black-Small | |
+-----------+---------------------+--------+
| 1 | 1-400-Black-Medium | |
+-----------+---------------------+--------+
| 1 | 2-100-Red-Small | |
+-----------+---------------------+--------+
| 1 | 2-100-Red-Medium | |
+-----------+---------------------+--------+
| 1 | 2-100-Yellow-Small | |
+-----------+---------------------+--------+
| 1 | 2-100-Yellow-Medium | |
+-----------+---------------------+--------+
| 1 | 2-100-Black-Small | |
+-----------+---------------------+--------+
| 1 | 2-100-Black-Medium | |
+-----------+---------------------+--------+
| 1 | 2-200-Red-Small | |
+-----------+---------------------+--------+
| 1 | 2-200-Red-Medium | |
+-----------+---------------------+--------+
| 1 | 2-200-Yellow-Small | |
+-----------+---------------------+--------+
| 1 | 2-200-Yellow-Medium | |
+-----------+---------------------+--------+
| 1 | 2-200-Black-Small | |
+-----------+---------------------+--------+
| 1 | 2-200-Black-Medium | |
+-----------+---------------------+--------+
| 1 | 2-300-Red-Small | |
+-----------+---------------------+--------+
| 1 | 2-300-Red-Medium | |
+-----------+---------------------+--------+
| 1 | 2-300-Yellow-Small | |
+-----------+---------------------+--------+
| 1 | 2-300-Yellow-Medium | |
+-----------+---------------------+--------+
| 1 | 2-300-Black-Small | |
+-----------+---------------------+--------+
| 1 | 2-300-Black-Medium | |
+-----------+---------------------+--------+
| 1 | 2-400-Red-Small | |
+-----------+---------------------+--------+
| 1 | 2-400-Red-Medium | |
+-----------+---------------------+--------+
| 1 | 2-400-Yellow-Small | |
+-----------+---------------------+--------+
| 1 | 2-400-Yellow-Medium | |
+-----------+---------------------+--------+
| 1 | 2-400-Black-Small | |
+-----------+---------------------+--------+
| 1 | 2-400-Black-Medium | |
+-----------+---------------------+--------+
| 1 | 3-100-Red-Small | |
+-----------+---------------------+--------+
| 1 | 3-100-Red-Medium | |
+-----------+---------------------+--------+
| 1 | 3-100-Yellow-Small | |
+-----------+---------------------+--------+
| 1 | 3-100-Yellow-Medium | |
+-----------+---------------------+--------+
| 1 | 3-100-Black-Small | |
+-----------+---------------------+--------+
| 1 | 3-100-Black-Medium | |
+-----------+---------------------+--------+
| 1 | 3-200-Red-Small | |
+-----------+---------------------+--------+
| 1 | 3-200-Red-Medium | |
+-----------+---------------------+--------+
| 1 | 3-200-Yellow-Small | |
+-----------+---------------------+--------+
| 1 | 3-200-Yellow-Medium | |
+-----------+---------------------+--------+
| 1 | 3-200-Black-Small | |
+-----------+---------------------+--------+
| 1 | 3-200-Black-Medium | |
+-----------+---------------------+--------+
| 1 | 3-300-Red-Small | |
+-----------+---------------------+--------+
| 1 | 3-300-Red-Medium | |
+-----------+---------------------+--------+
| 1 | 3-300-Yellow-Small | |
+-----------+---------------------+--------+
| 1 | 3-300-Yellow-Medium | |
+-----------+---------------------+--------+
| 1 | 3-300-Black-Small | |
+-----------+---------------------+--------+
| 1 | 3-300-Black-Medium | |
+-----------+---------------------+--------+
| 1 | 3-400-Red-Small | |
+-----------+---------------------+--------+
| 1 | 3-400-Red-Medium | |
+-----------+---------------------+--------+
| 1 | 3-400-Yellow-Small | |
+-----------+---------------------+--------+
| 1 | 3-400-Yellow-Medium | |
+-----------+---------------------+--------+
| 1 | 3-400-Black-Small | |
+-----------+---------------------+--------+
| 1 | 3-400-Black-Medium | |
+-----------+---------------------+--------+
Remarque: Il n'y a pas de limite pour le nombre de groupes, d'attributs et de valeurs d'attributs. Le résultat de l'exemple pourrait être quelque chose comme ceci:
Attributes:
+-------+------+-------+--------+
| Color | Size | Model | Gender |
+-------+------+-------+--------+
Combinations:
+------------------------------+
| 1-100-Red-Small-Model 1-Male |
+------------------------------+
| 1-100-Red-Small-Model 2-Male |
+------------------------------+
Il n'est pas nécessaire de le faire avec une requête SQL. Nous pouvons également le faire avec la méthode Laravel Query Builder.
Merci d'avance pour votre aide.
Vérifiez l'exemple SQL Fiddle
Réponses
3 CoffeeNeedCoffee Nov 17 2020 at 06:17
Cela devrait le faire. Ne fonctionnera pas dans mysql 5.7 car les CTE récursifs n'ont été inclus que plus tard, mais cela vous permettra d'avoir un nombre variable d'attributs et de valeurs_attributs par group_id. Le violon est là .
with recursive allAtts as (
/* Get our attribute list, and format it if we want; concat(a.title, ':', v.title) looks quite nice */
SELECT
att.group_id,
att.id,
CONCAT(v.title) as attDesc,
dense_rank() over (partition by att.group_id order by att.id) as attRank
FROM table_attributes att
INNER JOIN table_attribute_values v
ON v.group_id = att.group_id
AND v.attribute_id = att.id
),
cte as (
/* Recursively build our attribute list, assuming ranks are sequential and we properly linked our group_ids */
select group_id, id, attDesc, attRank from allAtts WHERE attRank = 1
union all
select
allAtts.group_id,
allAtts.id,
concat_ws('-', cte.attDesc, allAtts.attDesc) as attDesc,
allAtts.attRank
from cte
join allAtts ON allAtts.attRank = cte.attRank +1
AND cte.group_id = allAtts.group_id
)
/* Our actual select statement, which RIGHT JOINs against the table_groups
so we don't lose entries w/o attributes */
select
grp.id,
concat_ws('-', d.day, qty.quantity, cte.attDesc) as combinations
from cte
inner join (select group_id, max(attRank) as attID
from cte
group by group_id) m on cte.group_id = m.group_id and m.attID = cte.attrank
RIGHT JOIN table_groups grp ON grp.id = cte.group_id
LEFT JOIN table_days d on grp.id = d.group_id
LEFT JOIN table_quantities qty on grp.id = qty.group_id;