Supposons qu'un produits puisse avoir 0 ou plusieurs options - implémentées avec les tableaux suivants:
products - id - name options - id - name product_options - id - product_id - option_id
Supposons en outre les produits suivants ont les options suivantes:
Comment puis-je interroger ceci pour obtenir des résultats comme celui-ci:
Mes options sont en fait un arbre imbriqué. Et ils ont une clé étrangère vers une table de catégories (également une arborescence imbriquée). En fin de compte, je dois être capable de faire cette requête et de regrouper les résultats par catégorie. Cependant, je devrais probablement comprendre d'abord comment résoudre cette version plus simple de mon problème.
MISE À JOUR 1: Je ne sais pas à l'avance quelles pourraient être les options. De plus, il n'y a pas de limite au nombre d'options qu'un produit peut avoir.
4 Réponses :
Vous pouvez utiliser l'agrégation conditionnelle, si je comprends bien:
select po.product_id,
max(case when o.name = 'Option 1' then o.name end) as option_1,
max(case when o.name = 'Option 2' then o.name end) as option_2,
max(case when o.name = 'Option 3' then o.name end) as option_3,
max(case when o.name = 'Option 4' then o.name end) as option_4,
max(case when o.name = 'Option 5' then o.name end) as option_5
from product_options po join
options o
on po.option_id = o.id
group by po.product_id
Cela fonctionne si j'ai une liste finie d'options - et que je les connais à l'avance. Je ne sais pas quelles sont les options possibles.
@StackOverflowNewbie. . . Ensuite, vous auriez besoin d'un pivot dynamique. Une requête SQL renvoie un ensemble fixe de colonnes.
Aucune expérience avec les pivots dynamiques, mais au moins j'ai quelque chose à google maintenant. Je n'arrête pas de penser que je dois faire une sous-requête d'une manière ou d'une autre - pour obtenir d'abord la liste des options, puis essayer de faire quelque chose comme vous l'avez fait?
@StackOverflowNewbie. . . Vous pouvez le faire aussi.
Mon idée est-elle possible? Pouvez-vous suggérer comment cela peut être fait?
Ceci traite d'un compromis sur EAV.
Si vous avez un nombre inconnu d'options, vous pouvez utiliser une procédure stockée pour créer dynamiquement une requête qui peut être utilisée pour faire pivoter votre table. Quelque chose comme ceci:
name Option 1 Option 2 Option 3 Option 4 Option 5 Product 1 Option 1 Option 2 Option 3 Product 2 Option 2 Option 3 Option 4 Product 3 Option 3 Option 4 Option 5
Cette procédure produira une requête comme celle-ci (essentiellement la même que dans la réponse de @ GordonLinoff pour les exemples de données dans votre question):
SELECT p.name,
MAX(CASE WHEN o.name = 'Option 1' THEN o.name END) AS `Option 1`,
MAX(CASE WHEN o.name = 'Option 2' THEN o.name END) AS `Option 2`,
MAX(CASE WHEN o.name = 'Option 3' THEN o.name END) AS `Option 3`,
MAX(CASE WHEN o.name = 'Option 4' THEN o.name END) AS `Option 4`,
MAX(CASE WHEN o.name = 'Option 5' THEN o.name END) AS `Option 5`
FROM products p
JOIN product_options po ON po.product_id = p.id
JOIN options o ON o.id = po.option_id
GROUP BY p.name
Existe-t-il un moyen de le faire sans procédure stockée?
@StackOverflowNewbie Je ne le crois pas à cause du nombre variable de colonnes. Je surveillerai cependant d'autres réponses - peut-être que quelqu'un me prouvera le contraire.
Avez-vous vraiment besoin de structurer l'affichage au niveau de la base de données?
Honnêtement, si c'est faisable, je me sauverais la tête et exécuterais:
SELECT p.*, o.*
FROM product_options po
JOIN products p
ON p.id = po.product_id
JOIN options o
ON o.id = po.option_id
WHERE ...
ORDER BY ...
Vous pouvez ensuite trier la structure souhaitée au niveau de l'application au fur et à mesure que vous parcourez l'ensemble de résultats
Après tant d'essais, j'ai trouvé la requête ci-dessous qui retournera le résultat exact comme vous le souhaitez, même si les options et le produit peuvent augmenter.
SELECT CONCAT(P.productName, ",", GROUP_CONCAT(COALESCE(IF(P.optionId IN (product_options.option_id),P.optionName,NULL),"NULL") order by P.optionId)) AS result FROM product_options RIGHT JOIN (SELECT products.id as productId, products.name as productName,options.id as optionId, options.name AS optionName from products,options) as P On P.productId = product_options.product_id AND P.optionId = product_options.option_id GROUP BY P.productId
Cela ne fonctionne pas du tout. dbfiddle.uk/…
J'ai compris ! Vous le testez sur MySql 8, qui est sensible à la casse pour les identifiants. Dans ma requête plus tôt, j'ai manqué la sensibilité à la casse. J'ai mis à jour ma réponse, veuillez vérifier
combien d'options distinctes sont possibles?
... Avez-vous déjà essayé quelque chose?
@VamsiPrabhala - illimité.
Recommandation: supprimez le tableau des options en ne les normalisant pas. Vous utilisez le schéma EAV, qui ne s'adapte pas bien; en normalisant
option_namesvous aggravez les choses.Ces options sont censées augmenter / diminuer au fil du temps. Comment ne pas utiliser EAV? Chaque option doit devenir une colonne dans le tableau
products?