GROUP BY sur une requête pleine de jointures

**buxbux** · 14/12/2011, 19h46

Bonjour!
Je travaille sur une requête assez conséquente mais qui me pose un petite problème. Je n'arrive pas a obtenir une ligne de retour par élément recherché.

Voici la requête:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SELECT e0_.id AS id0, e0_.url AS url1, e0_.name AS name2, e0_.embed AS embed3, e0_.created AS created4, e0_.updated AS updated5, m1_.id AS id6, m1_.name AS name7, m1_.slug AS slug8, m1_.description AS description9, m1_.open AS open10, e2_.id AS id11, e2_.name AS name12, t3_.id AS id13, t3_.name AS name14, t3_.count AS count15, u4_.id AS id16, m5_.username AS username17, m5_.username_canonical AS username_canonical18, m5_.email AS email19, m5_.email_canonical AS email_canonical20, m5_.enabled AS enabled21, m5_.algorithm AS algorithm22, m5_.salt AS salt23, m5_.password AS password24, m5_.last_login AS last_login25, m5_.locked AS locked26, m5_.expired AS expired27, m5_.expires_at AS expires_at28, m5_.confirmation_token AS confirmation_token29, m5_.password_requested_at AS password_requested_at30, m5_.roles AS roles31, m5_.credentials_expired AS credentials_expired32, m5_.credentials_expire_at AS credentials_expire_at33, m5_.id AS id34, m5_.slug AS slug35, e0_.element_type_id AS element_type_id36, e0_.user_id AS user_id37, e0_.group_id AS group_id38, m1_.user_id AS user_id39, u4_.user_id AS user_id40, u4_.element_id AS element_id41 
FROM element e0_ 
LEFT JOIN m_group m1_ ON e0_.group_id = m1_.id 
LEFT JOIN element_type e2_ ON e0_.element_type_id = e2_.id 
LEFT JOIN elements_tag e7_ ON e0_.id = e7_.element_id 
LEFT JOIN tag t6_ ON t6_.id = e7_.tag_id 
LEFT JOIN elements_tag e8_ ON e0_.id = e8_.element_id 
LEFT JOIN tag t3_ ON t3_.id = e8_.tag_id 
LEFT JOIN users_elements_favorites u4_ ON e0_.id = u4_.element_id AND (u4_.user_id = ?) 
INNER JOIN m_user m5_ ON e0_.user_id = m5_.id 
ORDER BY e0_.created DESC, e0_.name DESC 
LIMIT 5

Pour ce qui reconnaisses c'est une requête SQL généré avec Doctrine (2.0). Il y a beaucoup de jointures, ce qui rend un peu moins lisible la requête veuillez m'excuser.

Le retour de cette requête ressemble a ça:

Code :

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

1
2
3
4
5
 
1  |  element1 | ...
2  |  element2 | ...
2  |  element2 | ...
3  |  element3 | ...

En gros, j'ai des doublons dans les résultats. Car ce que je cherche ici, ce sont des enregistrement de la table "element"
J'ai essayé avec un DISTINCT:

Code :

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

1
2
 
SELECT DISTINCT e0_.id AS id0 ...

Mais j'ai le même retour.

J'ai également essayé avec un GROUP BY:

Code :

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

1
2
3
4
5
[...]
INNER JOIN m_user m5_ ON e0_.user_id = m5_.id 
GROUP BY e0_.id, e2_.id, t3_.id, m5_.id, m1_.id, u4_.id 
ORDER BY e0_.created DESC, e0_.name DESC 
LIMIT 5

Mais les doublons sont toujours là. Il me semblerait bien pratique de faire:

Code :

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

1
2
3
4
5
[...]
INNER JOIN m_user m5_ ON e0_.user_id = m5_.id 
GROUP BY e0_.id
ORDER BY e0_.created DESC, e0_.name DESC 
LIMIT 5

Mais les colonnes inclus dans le select doivent apparaître dans le group by:

Code :

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

1
2
3
ERROR: column "m1_.id" must appear in the GROUP BY clause or be used in an aggregate function
État SQL :42803
Caractère : 139

Y a t-il une solution d'après vous?

**tatayo** · 14/12/2011, 20h20

Bonjour,
Si le distinct ne dédoublonne pas les lignes, c'est que les autres colonnes ne sont "pas en doublons".
Dans ce que je comprends, tu veux dédoublonner sur e0_.id AS id0 et e0_.url AS url1.
Mais pour chaque couple de valeur, quelles valeurs pour les autres colonnes faut-il prendre ? La plus petite, la plus grande, la dernière...

Tatayo.

**asmduty** · 15/12/2011, 09h25

Exact, et ton erreur dans le group by vient du fait que tous les champs du select (sauf fonctions) doivent être dans le group by.
Donc de toute manière tu te retrouverais avec les mêmes doublons.

**CinePhil** · 15/12/2011, 11h26

Moi je dirais que le problème vient peut-être un peu de là :

c'est une requête SQL généré avec Doctrine (2.0)

Les ORM ne sont vraiment pas top pour les requêtes complexes ; il vaut mieux les écrire en SQL natif et les soumettre ainsi au moteur de l'ORM directement.

Commence par regarder si tu as besoin de toutes ces colonnes dans ton jeu de résultats et en conséquence si tu as besoin de toutes ces jointures. Ta requête sera peut-être plus simple donc plus rapide à exécuter et le jeu de résultat moins gros donc moins lourd à transporter sur le réseau et à traiter dans le programme.

Bref, ne te laisse pas imposer la dictature de Doctrine !

**buxbux** · 15/12/2011, 18h43

Merci pour vos analyses :p
En principe tout ce qui est dans le select est utilisé lors du traitement.
En fait, si je comprend bien j'ai des données dans le select qui m'empêche de grouper par e0_.id.
Il faut donc que je repère qu'elles sont les données du select que je ne dois pas select.

Je vais chercher dans ce sens là, dites moi si je me trompes :p

**tatayo** · 15/12/2011, 19h01

Je dirai que si tu veux regrouper sur e0_.id uniquement, toutes les autres colonnes présentes dans la clause select doivent faire l'objet d'une fonction d'aggrégation (Max, Min, Avg...).

Tatayo.

**CinePhil** · 15/12/2011, 19h32

DISTINCT opère sur toutes les colonnes du SELECT.

Envoyé par buxbux

Car ce que je cherche ici, ce sont des enregistrement de la table "element"

Tu veux apparemment une seule ligne par élément et il est fort probable que chaque id soit unique dans la table element.

Alors je dirais comme Tatyo :

Envoyé par Tatayo

quelles valeurs pour les autres colonnes faut-il prendre ? La plus petite, la plus grande, la dernière...

**buxbux** · 15/12/2011, 19h52

Envoyé par CinePhil

Alors je dirais comme Tatyo :
Citation:
>Envoyé par Tatayo
>quelles valeurs pour les autres colonnes faut-il prendre ? La plus petite, la plus grande, la dernière...

Ah ça ... Le truc c'est que j'ai ajouté ces jointures au fur et a mesures de mes besoins en données. Dans ce sens je dirais que j'ai besoin de chacune d'entre elles. Cependant, ça empêche le sgbd de pouvoir sortir d'une ligne par élément donc.

Est-ce que le doublon d'enregistrement peut venir du fait qu'a deux reprise je join a mon élément deux fois la même relation ?
L'une pour un select, l'autre pour filtrer.
=> Je dirais que non, vu que ce n'est pas dans un select.

Qu'est-ce qui a ce moment la peut faire qu'il y est doublons sur un enregistrement élément ?

Je me sens en manque d'expérience dans ces moment la arf !

**CinePhil** · 15/12/2011, 21h07

Est-ce que le doublon d'enregistrement peut venir du fait qu'a deux reprise je join a mon élément deux fois la même relation ?
L'une pour un select, l'autre pour filtrer.
=> Je dirais que non, vu que ce n'est pas dans un select.

Et moi je dirais que oui !

Je n'avais pas encore regardé ta requête en détail.
La seule restriction que je vois est celle-ci :

Code :

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

AND (u4_.user_id = ?)

Par contre j'y trouve effectivement deux fois le même couple de jointures :

Code :

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

1
2
3
4
LEFT JOIN elements_tag e7_ ON e0_.id = e7_.element_id 
	LEFT JOIN tag t6_ ON t6_.id = e7_.tag_id 
LEFT JOIN elements_tag e8_ ON e0_.id = e8_.element_id 
	LEFT JOIN tag t3_ ON t3_.id = e8_.tag_id

Une seule fois suffirait !

D'après les jointures, tu as le schéma suivant :
m_group -0,n----appartenir-------------------1,1- element - 1,1----typer----0,n- element_type
tag -0,n----elements_tag----0,n-------------------------|
m_user -0,n----user_elements_favorites----0,n---|

Comme pour un élément il peut y avoir plusieurs tags, cela peut aussi générer des plusieurs lignes dans le jeu de résultat de la requête.

Un élément peut aussi être favori de plusieurs utilisateurs mais comme on restreint à un seul utilisateur, cette association (0,n - 0,n) ne générera pas plusieurs lignes pour un élément dans le résultat de cette requête.

La requête remise en forme pour une lecture plus facile :

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
SELECT e0_.id AS id0, 
	e0_.url AS url1, 
	e0_.name AS name2, 
	e0_.embed AS embed3, 
	e0_.created AS created4, 
	e0_.updated AS updated5, 
	m1_.id AS id6, 
	m1_.name AS name7, 
	m1_.slug AS slug8, 
	m1_.description AS description9, 
	m1_.open AS open10, 
	e2_.id AS id11, 
	e2_.name AS name12, 
	t3_.id AS id13, 
	t3_.name AS name14, 
	t3_.count AS count15, 
	u4_.id AS id16, 
	m5_.username AS username17,
	m5_.username_canonical AS username_canonical18, 
	m5_.email AS email19, 
	m5_.email_canonical AS email_canonical20, 
	m5_.enabled AS enabled21, 
	m5_.algorithm AS algorithm22, 
	m5_.salt AS salt23, 
	m5_.password AS password24, 
	m5_.last_login AS last_login25, 
	m5_.locked AS locked26, 
	m5_.expired AS expired27, 
	m5_.expires_at AS expires_at28, 
	m5_.confirmation_token AS confirmation_token29, 
	m5_.password_requested_at AS password_requested_at30, 
	m5_.roles AS roles31, 
	m5_.credentials_expired AS credentials_expired32, 
	m5_.credentials_expire_at AS credentials_expire_at33, 
	m5_.id AS id34, 
	m5_.slug AS slug35, 
	e0_.element_type_id AS element_type_id36, 
	e0_.user_id AS user_id37, 
	e0_.group_id AS group_id38, 
	m1_.user_id AS user_id39, 
	u4_.user_id AS user_id40, 
	u4_.element_id AS element_id41 
FROM element e0_ 
LEFT JOIN m_group m1_ ON e0_.group_id = m1_.id 
LEFT JOIN element_type e2_ ON e0_.element_type_id = e2_.id 
LEFT JOIN elements_tag e7_ ON e0_.id = e7_.element_id 
	LEFT JOIN tag t6_ ON t6_.id = e7_.tag_id 
LEFT JOIN elements_tag e8_ ON e0_.id = e8_.element_id 
	LEFT JOIN tag t3_ ON t3_.id = e8_.tag_id 
LEFT JOIN users_elements_favorites u4_ ON e0_.id = u4_.element_id AND (u4_.user_id = ?) 
INNER JOIN m_user m5_ ON e0_.user_id = m5_.id 
ORDER BY e0_.created DESC, e0_.name DESC 
LIMIT 5

**SQLpro** · 15/12/2011, 23h30

Encore une fois et comme on vous l'as déjà dite, il n'a a pas de doublon dans les résultats de votre requête. Avec DISTINCT toutes les lignes sorties sont unique dans l'ensemble des valeurs de la ligne.

Votre problème vient de deux choses :
1) vous ne savez pas ce que vous voulez, car vous demandez à la fois des données de synthèses et du détail. Or dans la vie il faut choisir !
2) vous ne comprenez visiblement pas le langage SQL, ni les concepts ensemblistes. Une formation sur SQL et les concepts des SGBDR me parait nécessaire

Enfin, l'utilisation d'un ORM est généralement une vaste connerie. Lire l'article que j'ai écrit à ce sujet : http://sqlpro.developpez.com/cours/b...s-epaisses.pdf

A +

**buxbux** · 16/12/2011, 10h12

Eh bien je vous remercie pour tout vos conseils. Je m'attendais a me faire lyncher un petit peu. J'ai toujours eu du mal a saisir le SQL de façon poussé, même pendant mes études ...
Je vais travailler cette requête et lire votre article.

**buxbux** · 18/12/2011, 17h57

J'ai réfléchi de nouveau à mon problème et ai mieux compris mon erreur (je crois). J'ai ouvert un autre sujet posant la question liée à ma problématique:
http://www.developpez.net/forums/d11...s/#post6405759

**ange16** · 18/09/2012, 14h14

Envoyé par SQLpro

Enfin, l'utilisation d'un ORM est généralement une vaste connerie. Lire l'article que j'ai écrit à ce sujet : http://sqlpro.developpez.com/cours/b...s-epaisses.pdf
A +

Se citer pour argumenter sur ses propres propos, voilà la plus vaste connerie que j'aurai noté de ce post.

Au delà je dirai qu'il faut maîtriser les limites d'un ORM avant de taper dessus, on ne peut avoir un contrôle total tout en ayant une abstraction easy-to-use.
Le dQl pallie partiellement à certaines requêtes, mais le souci initial n'est pas un souci de requête mais bien souvent de conception: si vos requêtes sont infaisables c'est peut être que vous auriez du tout simplement modifier votre structure d'origine.

Cdt,

m'excusant, j'arrive un peu tard monsieur l'expert

GROUP BY sur une requête pleine de jointures

Langage SQL

Discussions similaires

Partager

Partager