Filtre d'une requête One-to-many

**CaraG33k** · 16/05/2019, 10h58

Bonjour,

Je cherche à récupérer des entités d'une table en fonction de conditions établies sur une table jointe et le tout en une seul requête SQL... jusque là rien de très complexe sauf si la condition est une condition d'exclusion.

Le contexte (simplifié) :

Table 1

id	person
1	person1
2	person2
3	person3

Table 2

id	personid	register
1	1	date1
2	1	date2
3	2	date1
4	3	date3

Donc si je veux les Person ayant Register = date1, je fais simplement un INNER JOIN avec WHERE register=date1. Facile.
Mais si je ne veux afficher que les Person n'ayant pas Register = date1 (donc WHERE register<>date1) sous le même modèle, je vais obtenir :

person	register
person1	date2
person3	date3

Alors que je souhaiterais ne récupérer que person3.

Bien évidemment les deux tables doivent contenir des centaines de lignes et donc je doute que l'utilisation de requête multiples, voire le traitement ultérieur en PHP - c'est un projet web -, soit une bonne idée en terme de performances.
Idéalement, il faudrait pouvoir le faire en une seule requête SQL... mais je n'arrive pas à penser mieux que deux requêtes : l'une pour récupérer l'id de toutes les person ayant un register=date1 et l'autre pour récupérer les person avec un NOT IN (la liste des id de la première requête).
Y a-t-il plus intelligent à faire (je l'espère) ? J'ai le sentiment que c'est une situation classique mais ni Qwant ni le moteur du forum ne m'ont permis de trouver une piste au milieu de tout le bruit généré par les jointures et les one-to-many/many-to-many.

Merci du coup de main !

**tatayo** · 16/05/2019, 11h14

Bonjour,
Tu peux utiliser un NOT EXISTS, une jointure externe…
Exemple avec la jointure:

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
select 1
from Table1
left outer join Table2 on Table1.id = Table2.id2
where Table2.id2 is null

Maintenant ton exemple ne colle pas avec ta demande. Tu veux les personnes "qui n'ont pas de register = date1", seule la ligne de Personne3 remplit cette condition. Personne1 a bien une ligne avec register = Date1 (celle avec l'id 1).
L'exemple donné correspond aux personnes qui "ont un register différent de date1". Ce n'est pas la même chose !

Tatayo.

**CaraG33k** · 16/05/2019, 11h41

Je me suis exprimé en mêlant langage naturel et SQL désolé. Pour clarifier, ce que je veux c'est récupérer les person qui n'ont pas de lien avec un register qui aurait pour valeur date1 (et donc dans l'exemple ne récupérer que person3 et non pas person1 qui est lié à date2 mais également à date1). Et donc ta remarque définit bien mon problème : je veux récupérer ceux qui ne sont pas lié aux register=date1 alors que jusque là je n'arrive (directement avec les jointures et conditions) à ne récupérer que le ceux qui ont (notamment) un register<>date1.

Je vais tester avec une jointure externe mais du coup, je me demande si j'en ai bien compris le fonctionnement. ^^
Merci de ta réponse !

Edit : Bah non... le LEFT OUTER JOIN, c'est déjà ce que j'utilise et qui me sort la ligne person1 dont je ne veux pas.
Je précise ma requête (toujours en la simplifiant) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT * 
FROM documents d0_ 
INNER JOIN person p1_ ON d0_.person_id = p1_.id
LEFT JOIN register r4_ ON p1_.id = r4_.person_id 
WHERE 1 <> 0 AND d0_.expired_on = "date0" AND r4_.expired_on <> "date1"

**al1_24** · 16/05/2019, 11h51

Tu cherches donc les lignes de table1 pour lesquelles il n'existe pas de ligne correspondante dans table2 avec la valeur date1 dans register...
Toute la requête est dans cette phrase

**Michel.Priori** · 16/05/2019, 12h19

Envoyé par CaraG33k

Edit : Bah non... le LEFT OUTER JOIN, c'est déjà ce que j'utilise et qui me sort la ligne person1 dont je ne veux pas.
Je précise ma requête (toujours en la simplifiant) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT * 
FROM documents d0_ 
INNER JOIN person p1_ ON d0_.person_id = p1_.id
LEFT JOIN register r4_ ON p1_.id = r4_.person_id 
WHERE 1 <> 0 AND d0_.expired_on = "date0" AND r4_.expired_on <> "date1"

Pour moi la conditionnelle proposée par tatayo n'est pas présente dans le bout de code.

**CaraG33k** · 16/05/2019, 13h00

Envoyé par al1_24

Tu cherches donc les lignes de table1 pour lesquelles il n'existe pas de ligne correspondante dans table2 avec la valeur date1 dans register...
Toute la requête est dans cette phrase

Je dois avouer ne pas bien cerner la directive NOT EXISTS... elle se fait sans jointure ? La sous-requête ne concerne alors que la toute dernière table, c'est bien ça ?

Edit : en lisant la doc, j'ai tenté une requête... mais je pense que je fais pas correctement... puisque ça ne me renvoit aucune entité...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT * 
FROM documents d0_ 
INNER JOIN person p1_ ON d0_.person_id = p1_.id
LEFT JOIN register r4_ ON p1_.id = r4_.person_id 
WHERE 1 <> 0 AND d0_.expired_on = "date0" AND NOT EXISTS (SELECT 1 AS sclr_47 FROM register r4_ WHERE r4_.expired_on = "date1")

Envoyé par Michel.Priori

Pour moi la conditionnelle proposée par tatayo n'est pas présente dans le bout de code.

Tu veux parler du `WHERE Table2.id IS NULL` ? Pour moi c'est un exemple de condition car en toute logique cette condition renvoit toujours false dans mes tables...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT * 
FROM documents d0_ 
INNER JOIN person p1_ ON d0_.person_id = p1_.id
LEFT JOIN register r4_ ON p1_.id = r4_.person_id 
WHERE 1 <> 0 AND d0_.expired_on = "date0" AND r4_.expired_on <> "date1" AND r4_.person_id IS NULL

Je confirme que cette requête ne renvoit plus aucune entité. Désolé de faire mon boulet.

**aieeeuuuuu** · 16/05/2019, 13h33

bonjour,

Envoyé par CaraG33k

Tu veux parler du `WHERE Table2.id IS NULL` ? Pour moi c'est un exemple de condition car en toute logique cette condition renvoit toujours false dans mes tables...

Dans les tables oui, mais suite à la jointure externe, s'il n'y a pas de correspondance dans la table register (ce que vous cherchez, donc) , alors ses colonnes seront à NULL.

Envoyé par CaraG33k

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT * 
FROM documents d0_ 
INNER JOIN person p1_ ON d0_.person_id = p1_.id
LEFT JOIN register r4_ ON p1_.id = r4_.person_id 
WHERE 1 <> 0 AND d0_.expired_on = "date0" AND r4_.expired_on <> "date1" and AND r4_.person_id IS NULL

Je confirme que cette requête ne renvoit plus aucune entité. Désolé de faire mon boulet.

[/quote]
C'est normal, vous avez mal placé la condition sur la date, il faut la mettre dans la condition de jointure. Si vous le mettez dans le WHERE, cela annule l'effet de la jointure externe : les lignes qui n'ont pas de correspondance sont éliminées du résultat, car NULL n'est pas différent de date1

**Michel.Priori** · 16/05/2019, 13h43

Est-ce ces exemples sont l'expression de ce que tu cherches :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
Create table table1 
(id int
,person varchar(10)
);
 
Create table table2
(id int
,personid int
,register varchar(10)
);
 
insert into table1 Values 
 (1,'person1')
,(2,'person2')
,(3,'person3')
 
insert into table2 values
 (1,1,'date1')
,(2,1,'date2')
,(3,2,'date1')
,(4,3,'date3')
 
 
/* afficher les informations 
     - de table1
    - qui sont sans correspondance avec les lignes de table2 limitée à la 'date1' dans register
*/
;with table2_avec_date1 as
( select * from Table2 where register ='date1')
select t1.*
from Table1 t1
    left outer join table2_avec_date1 t2
    on T1.id = T2.personid
where T2.id is null
;
/* afficher les informations 
       - de table1 pour lesquelles il n'existe pas de ligne correspondante dans 
          - table2 avec la valeur date1 dans register
*/
select *
from Table1 t1
where not exists(select null
              from Table2 t2
              where t2.register ='date1'
              and t2.personid=t1.id)
;
 
/* afficher les informations 
       - de table1 pour lesquelles il n'existe pas de valeur correspondantes à
          - la liste des personid de table2 ayant la valeur date1 dans register
*/
Select *
from table1 t1
where t1.id not in (select t2.personid
                from Table2 t2 
                where t2.register ='date1')

**CaraG33k** · 16/05/2019, 16h03

Envoyé par aieeeuuuuu

C'est normal, vous avez mal placé la condition sur la date, il faut la mettre dans la condition de jointure. Si vous le mettez dans le WHERE, cela annule l'effet de la jointure externe : les lignes qui n'ont pas de correspondance sont éliminées du résultat, car NULL n'est pas différent de date1

Ok ! Je pense avoir compris la jointure. Je ne connaissais pas l'usage du WHERE. J'ai regardé dans la doc et dans les fichiers de Doctrine que j'utilise : la fonction leftJoin() proposée ne prend qu'un paramètre WITH mais qui ne semble pas convenir puisqu'il se contente d'ajouter un AND au ON.

Concernant l'alternative du NOT EXISTS, je galère à bidouiller un truc qui marche. Par contre, ça m'a fait penser à la syntaxe du NOT IN et j'ai bien le résultat que je souhaite en faisant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SELECT * 
FROM documents d0_ 
INNER JOIN person p1_ ON d0_.person_id = p1_.id
LEFT JOIN register r4_ ON p1_.id = r4_.person_id 
WHERE 1 <> 0 AND d0_.expired_on = "date0"
AND p1_.id NOT IN (SELECT r4_.person_id AS sclr_47 FROM register r4_ WHERE r4_.expired_on = "date1")

Bon j'ai lu que NOT EXISTS était préférable à NOT IN pour des raisons de performances donc je vais potasser tes exemples, Michel.Priori. D'ailleurs, intuitivement, je n'attends pas de différence entre le 2e et le 3e, si ?

**aieeeuuuuu** · 16/05/2019, 17h26

Envoyé par CaraG33k

mais qui ne semble pas convenir puisqu'il se contente d'ajouter un AND au ON.

Si, c'est exactement cela.

ce qui devrait donner

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
SELECT * 
FROM documents d0_ 
INNER JOIN person p1_ ON d0_.person_id = p1_.id
LEFT JOIN register r4_ ON p1_.id = r4_.person_id AND r4_.expired_on <> "date1"
WHERE 1 <> 0 AND d0_.expired_on = "date0"  and AND r4_.person_id IS NULL

en déplaçant la condition r4_.expired_on <> "date1" du WHERE vers la condition de jointure

**CaraG33k** · 16/05/2019, 19h20

Ça marche ! Et j'aime bien la syntaxe (même si en faisant un test rapide sur une petite BDD, je n'ai pas de différence significative avec le NOT IN en terme de performances) !
Je marque donc « résolu » et me garde pour plus tard la meilleure compréhension du NOT EXISTS.

Merci à tous !

**Michel.Priori** · 17/05/2019, 08h57

Envoyé par CaraG33k

en faisant un test rapide sur une petite BDD, je n'ai pas de différence significative

L'efficacité d'un moteur de résolution de plan d’exécution (optimizer) se mesure à trouver systématiquement le même plan (le meilleur) quelle que soit l'écriture de la requête.
Ça c'est la théorie.
La réalité est plus nuancée.
Faites des tests de montée en charge (cohérents avec votre projet) pour voir l'évolution dans le choix des plans d’exécution.
Ou alors, n'hésitez pas à mettre en commentaire dans votre code les alternatives pour le jour où vous aurez à faire une revisite pour raison d'optimisation.

Filtre d'une requête One-to-many

Langage SQL

Discussions similaires

Partager

Partager