J'ai deux tables:
personne
SELECT person.*
FROM person
INNER JOIN person_fields
ON person.id = person_fields.person_id
WHERE
(person_fields.type = 'isHappy' AND person_fields.value IS TRUE)
AND
(person_fields.type = 'hasFriends' AND person_fields.value IS TRUE)
person_fields
+-----+------------+-----------+-------+ | id | type | person_id | value | +-----+------------+-----------+-------+ | 1 | isHappy | 1 | 1 | | 2 | hasFriends | 1 | 1 | | 3 | hasFriends | 2 | 1 |
Je souhaite sélectionner toutes les personnes de personne pour lesquelles isHappy ET hasFriends est TRUE. Voici ce que j'ai essayé:
+-----+------------+---------------+ | id | name | address | +-----+------------+---------------+ | 1 | John Smith | 123 North St. | | 2 | Joe Dirt | 456 South St. | +-----+------------+---------------+
Malheureusement, cela ne fonctionne pas car vous ne pouvez pas avoir un seul enregistrement dans person_fields qui contient type = 'isHappy' AND type = 'hasFriends' . Je ne peux pas OU ces deux conditions parce que cela renverrait à la fois John Smith et Joe Dirt, mais je ne veux que John Smith parce qu'il est le seul à être heureux et à avoir des amis en même temps.
Des suggestions? Merci d'avance!
4 Réponses :
En rejoignant deux fois:
SELECT person.*
FROM person
INNER JOIN person_fields happy
ON person.id = happy.person_id AND happy.type='isHappy' AND happy.value
INNER JOIN person_fields friends
ON person.id = friends.person_id AND friends.type='hasFriends' AND friends.value
Je vois. Cela semble logique. La seule chose sur laquelle je suis un peu hésitant est la suivante: si je voulais trouver toutes les personnes pour lesquelles 12 champs de la table person_fields étaient vrais, je devrais rejoindre 12 fois?
Ouais, je suppose que tu as raison! Je vais probablement tester cela demain et ensuite le marquer comme réponse. À long terme, je devrai probablement repenser la structure. Merci!
plus de colonnes dans la table des personnes est le moyen le plus simple. Si vous changez de colonne semi-fréquemment, peut-être colonnes dynamiques . L'indexation est utile, mais vous devez savoir quelles requêtes sont utilisées.
Je ne sais pas pourquoi vous avez été critiqué, mais cela fonctionne totalement. Je suppose que ce n'est pas aussi lisible que la réponse de @vogomatix.
J'ai voté pour :) Je n'ai même pas remarqué qu'il était là lorsque j'ai publié ma réponse similaire.
Si vous savez toujours combien de conditions vous voulez vérifier et que vous voulez toujours renvoyer uniquement les enregistrements person qui ont toutes les conditions, vous pouvez utiliser une sous-requête et modifier le HAVING clause pour sélectionner le nombre maximum de type parmi person_fields:
id name types 1 John Smith 2
Résultat:
SELECT p.id, p.name, (SELECT COUNT(DISTINCT pf.type) FROM person_fields AS pf WHERE pf.person_id = p.id and pf.value = true) AS types FROM person AS p HAVING types = 2
Qu'est-ce que j'ai raté?
Je ne sais pas non plus ce que vous avez manqué ... J'aime cette technique. Malheureusement, j'utilise Sequelize ORM et bien que vous puissiez utiliser une clause HAVING avec elle, ma requête est en fait assez complexe et ne permet pas cette option.
Vous pouvez rejoindre person_fields deux fois, une fois pour isHappy et une fois pour hasFriends .
AND f1.value = 1 AND f2.value = 1
Je ne sais pas où le champ value entre en jeu, mais vous pouvez lancer une condition supplémentaire OU deux si vous en avez besoin
SELECT p.* FROM person p INNER JOIN person_fields f1 ON p.id = f1.person_id INNER JOIN person_fields f2 ON f1.person_id = f2.person_id WHERE f1.type = 'isHappy' AND f2.type = 'hasFriends'
Cela et la réponse de @danblack s'exécutent à peu près en même temps et sont les plus rapides de toutes les réponses. Celui-ci est un peu plus lisible.
La solution standard ressemble à ceci:
SELECT person_id
FROM person_fields
WHERE type IN ('ishappy','hasfriends')
GROUP
BY person_id
HAVING COUNT(1) = 2;
... où '2' est égal au nombre d'arguments dans IN ()
Notez que cela suppose que (person_id, type) est UNIQUE
Je suppose que j'aurais dû être plus explicite à ce sujet, mais je dois SELECT * FROM person . Cela ne me donne que le person_id .
Heureusement, je suis sûr que vous avez les compétences nécessaires pour comprendre cela par vous-même!
Avec certitude! Certaines des autres réponses s'exécutent plus rapidement, mais c'est une autre option qui pourrait fonctionner.
Hmm, imaginez si vous aviez cinq ou six critères. Toutes ces jointures deviendraient un peu fastidieuses, non? Mais tout ce qui fait flotter votre bateau.
Convenu. C'est une chose que j'avais mentionnée à @danblack plus tôt. Cependant, je construis ces clauses de jointure en javascript en utilisant Sequelize ORM, donc ce n'est vraiment pas un problème.
Je pense toujours que les échelles ci-dessus sont meilleures et devraient être extrêmement rapides sur une table indexée
Vous avez peut-être raison, mais cela ne fonctionne pas bien avec Sequelize. Cela étant dit, cela peut être la solution parfaite pour quelqu'un d'autre (ou même moi) dans le futur.
D'accord, après avoir apporté d'autres modifications à ma requête, il s'avère que celle-ci fonctionne un peu mieux. J'ai fini par utiliser une requête brute dans Sequelize, c'est donc maintenant une option. Merci!
Je tiens à souligner que la solution ci-dessus ne fonctionne pas sur le problème comme indiqué car elle ne parvient pas à vérifier la colonne valeur . Une personne maléfique pourrait définir l'une des valeurs sur false / 0 et ruiner votre journée entière. Nécessite un AND value IS TRUE dans la clause where
De plus, isHappy et hasFriends peuvent être des chaînes sensibles à la casse, bien qu'une valeur énumérée serait probablement meilleure.
La pédanterie @vogomatix vous mènera partout ;-)