Discussion :

[dovmeup]

Bonjour,

Comment faire une pagination propre à partir de 2 tables jointes (LEFT), avec une clause WHERE sur la table jointe ?

Ce qui marche actuellement sans clause WHERE:

Code :

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

1
2
3
4
5
6
7
8
9
 
SELECT o.*, ot.`tag`, ot.`value` as `tag_value`
FROM (
	SELECT o.* FROM `nx_order` AS o
	ORDER BY o.`date_order`
	LIMIT 20, 40
) AS o
LEFT JOIN `nx_order_tag` AS ot
	ON (ot.`order_id` = o.`id`)

Mais si je veux ajouter un filtre sur la table ot, par ex:

Code :

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

1
2
3
 
	WHERE
		(ot.tag <> 'E')

je suis coincé: la clause WHERE mise simplement dans la sous-requête génère une erreur (la table ot n'est pas connue);
et en ajoutant un autre LEFT JOIN dans la sous-requête, pour y faire rentrer la table ot, avec un GROUP BY sur la clé primaire de nx_order, la pagination devient erronée...

Merci pour vos idées et conseils.

[diablo-dz]

Bonsoir
t'as essayé avec avec cette requête

Code :

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

1
2
3
4
5
6
7
8
SELECT o.*, ot.`tag`, ot.`value` AS `tag_value`
FROM (
	SELECT o.* FROM `nx_order` AS o
	ORDER BY o.`date_order`
	LIMIT 20, 40
) AS o
LEFT JOIN `nx_order_tag` AS ot
	ON (ot.`order_id` = o.`id`) AND (ot.tag <> 'E')

A+

[dovmeup]

Bonsoir
t'as essayé avec avec cette requête

Code :

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

1
2
3
4
5
6
7
8
SELECT o.*, ot.`tag`, ot.`value` AS `tag_value`
FROM (
	SELECT o.* FROM `nx_order` AS o
	ORDER BY o.`date_order`
	LIMIT 20, 40
) AS o
LEFT JOIN `nx_order_tag` AS ot
	ON (ot.`order_id` = o.`id`) AND (ot.tag <> 'E')

A+

Ça semble le faire au niveau du filtrage, merci.

En revanche un autre problème que je n'avais pas remarqué auparavant empêche de tester la pagination:

cette requête retourne en effet systématiquement un nombre de résultats correspondant au nombre de lignes dans la table jointe ot, sans semble-t-il tenir compte du LIMIT de la sous-requête...

Je ne l'avais pas vu parce qu'il se trouve que par hasard la table ot contient actuellement 40 lignes, ce qui correspondait à la valeur de LIMIT utilisée jusqu'ici.

Si je change LIMIT à 20,30 ou 20,50 , j'obtiens toujours ... 40 résultats !

Merci pour vos idées et éventuellement explications.

[dovmeup]

Bon, je reformule pour tenter de mettre plus clairement en évidence la problématique.

Table "t1": champs "id1" (clé primaire), "date"
contient des enregistrements datés.

Table "t2": champs "id2" (clé primaire), "id1", "tag", "value"
contient des propriétés (tag) d'éléments de la table t1,
avec chaque élément de t1 pouvant avoir de 0 à n propriétés.

J'ai besoin de pouvoir extraire de manière paginée n éléments de t1, avec leurs propriétés (ou pas s'ils n'en ont pas), avec un filtre d'exclusion sur 1 ou plusieurs
propriétés.

Par ex, sortir les éléments 20 à 40 de t1 n'ayant pas QUE la propriété "E" (les éléments ayant plusieurs propriétés dont la "E" sont acceptés):

La dernière requête tentée pour ce faire est la suivante:

Code :

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

1
2
3
4
5
6
7
8
SELECT t3.*, t2.`tag`, t2.`value` AS `tag_value`
FROM (
  SELECT * FROM `t1`
  ORDER BY `date`
  LIMIT 20, 20
) AS t3
LEFT JOIN `t2`
  ON (t2.`id1` = t1.`id1` AND t2.`tag` <> 'E')

Mais au moins 2 problèmes ici:
- elle sort un nombre de résultats égal au nombre total de lignes dans t2, et ne semble donc pas tenir compte du LIMIT de la sous-requête, ce même si t1 contient un grand nombre de lignes (?) ;
- le filtrage "t2.tag <> 'E'" dans la clause ON de la requête principale va théoriquement fausser la pagination (si on parvient à faire fonctionner le LIMIT dans la sous-requête);

Il faudrait logiquement mettre le filtre dans la sous-requête, pour que la table temporaire t3 contiennent les résultats de base filtrés, ordonnés et paginés; mais pour ce faire la table t2 doit y être connue, sans faire de JOIN pour préserver la pagination, et sans non plus à priori pouvoir faire de corrélation de table entre requête principale et sous requête (il me semble pas supportée pour les sous-requêtes FROM dans MySQL 5.5).

Merci pour vos idées.

[punkoff]

bonjour,


je ne comprend pas a quoi sert votre sous-requete dans ce cas là.

bref simplifiez, et le résultat devrait être bon :

Code :

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

1
2
3
4
5
6
 
SELECT t1.*, t2.`tag`, t2.`value` AS `tag_value`
FROM `t1`
LEFT JOIN `t2` ON (t2.`id1` = t1.`id1` AND t2.`tag` <> 'E')
ORDER BY t1.date
limit 20, 30

[dovmeup]

bonjour,


je ne comprend pas a quoi sert votre sous-requete dans ce cas là.

bref simplifiez, et le résultat devrait être bon :

Code :

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

1
2
3
4
5
6
 
SELECT t1.*, t2.`tag`, t2.`value` AS `tag_value`
FROM `t1`
LEFT JOIN `t2` ON (t2.`id1` = t1.`id1` AND t2.`tag` <> 'E')
ORDER BY t1.date
limit 20, 30

Merci pour votre réponse.

N'étant pas expert SGBD, mon raisonnement n'est peut-être effectivement pas le bon, mais si je suis votre suggestion, la pagination ne sera pas bonne puisqu'elle doit se faire uniquement sur la table principale t1 et non sur la jointure t1+t2 qui introduit potentiellement des lignes redondantes (éliminées en amont par un script PHP qui met les propriétés issues de t2 dans un tableau pour chaque ligne de t1), d'où l'intérêt supposé de la sous-requête qui est censée réaliser le filtrage et la pagination AVANT la jointure.

J'ai aussi envisagé l'utilisation de GROUP_CONCAT pour parvenir à mes fins, mais ayant souvent lu que cette fonctionnalité était si possible à éviter, la sous-requête me semblait être, peut-être à tort, la plus appropriée.

[punkoff]

Que voulez-vous comme rendu au final ?

Vous avez différente possibilités d'approcher le problème mais aucune ne conviennent ?

[dovmeup]

Que voulez-vous comme rendu au final ?

Vous avez différente possibilités d'approcher le problème mais aucune ne conviennent ?

La difficulté réside, pour moi, dans la pagination qui doit s'appliquer à la clé primaire de t1 et non aux lignes issues de la jointure puisqu'in fine les propriétés sont regroupées par un script PHP pour chaque ligne de t1 : en d'autres termes, si les pages contiennent 3 items, la colonne 'id1' (clé primaire t1) doit contenir très exactement 3 valeurs distinctes parmi les 3 ou plus lignes de résultats jointes (hors dernière page).

Bon, comme je ne suis toujours pas sûr d'avoir été clair, démonstration par l'exemple:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT * FROM t1
 
id1 date
---+----------
  1 2014-05-12
  2 2014-05-01
  3 2014-04-08
  4 2014-05-16
  5 2014-05-04
  6 2014-05-21
  7 2014-04-30
  8 2014-04-07
  9 2014-05-09

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
SELECT * FROM t2
 
id2 id1 tag value
---+---+---+-----
  1   2   O 12345
  2   3   E 12345
  3   3   O 12345
  4   3   X 12345
  5   4   E 12345
  6   4   A 12345
  7   5   O 12345
  8   5   E 12345
  9   5   A 12345
 10   5   X 12345
 11   5   Z 12345
 12   6   E 12345
 13   6   M 12345
 14   7   E 12345
 15   8   O 12345
 16   8   X 12345
 17   9   Z 12345

Résultat attendu avec:
- pages de 3 items,
- filtrage propriété 'E',
- dates décroissantes

Page 1:

Code :

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

1
2
3
4
5
id1 date       tag value
---+----------+---+-----
  6 2014-05-21  M  12345
  4 2014-05-16  A  12345
  9 2014-05-09  Z  12345

Page 2:

Code :

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

1
2
3
4
5
6
7
8
9
id1 date       tag value
---+----------+---+-----
  5 2014-05-04  O  12345
  5 2014-05-04  A  12345
  5 2014-05-04  X  12345
  5 2014-05-04  Z  12345
  2 2014-05-01  O  12345
  3 2014-04-08  O  12345
  3 2014-04-08  X  12345

Page 3:

Code :

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

1
2
3
4
id1 date       tag value
---+----------+---+-----
  8 2014-04-07  O  12345
  8 2014-04-07  X  12345

Après chaque requête, un script PHP ou autre finalise la collection avec une 2ème passe du style:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
while ($row = next($rowset))
{
  if (! isset($result[$row->id1])) {
    $result[$row->id1] = (object) array(
                                      'id1' => $row->id1,
                                      'date' => $row->date,
                                      'property' => array()
                                   );
  }
 
  $result[$row->id1]->property[$row->tag] = $row->value;
}

Et on obtient donc in fine la pagination souhaitée.

Maintenant si un expert MySQL m'explique que le mieux serait de faire ça avec un GROUP_CONCAT (ou bien que c'est la seule solution) côté SQL, je suis prêt à l'entendre , c'est juste que j'ai souvent lu et entendu dire que les requêtes avec GROUP_CONCAT c'est très vilain ...

Je ne suis pas non plus bloqué sur l'idée de la sous-requête FROM, c'est simplement ce qui m'est apparu au départ comme étant la solution (apparemment) la plus naturelle pour atteindre l'objectif.

Merci.

[aieeeuuuuu]

comme ceci :

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
 
SELECT t.id1, t.dte, t2.tag, t2.valeur
FROM
(
	SELECT id1, dte
	FROM t1
	WHERE EXISTS(
		SELECT 1
		FROM t2
		WHERE t2.id1 = t1.id1
		AND t2.tag <> 'E'
	)
	ORDER BY Dte DESC
	LIMIT 3,6
) t
INNER JOIN t2
	ON t2.id1 = T.id1
WHERE t2.tag <> 'E'
ORDER BY t.Dte DESC

[aieeeuuuuu]

ou encore :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SELECT A.id1, A.dte, X.tag, X.valeur
FROM   t1 AS A
INNER JOIN t2 AS X
    ON X.id1 = A.id1
LEFT JOIN t1 AS B
		INNER JOIN t2 AS Y
			ON Y.id1 = B.id1
			AND Y.tag <> 'E'
	ON B.dte > A.dte
WHERE X.tag <> 'E'
GROUP BY A.id1, A.dte,  X.tag, X.valeur
HAVING COUNT(DISTINCT B.id1) BETWEEN 3 AND 5 
ORDER BY A.dte DESC

[dovmeup]

comme ceci :

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
 
SELECT t.id1, t.dte, t2.tag, t2.valeur
FROM
(
	SELECT id1, dte
	FROM t1
	WHERE EXISTS(
		SELECT 1
		FROM t2
		WHERE t2.id1 = t1.id1
		AND t2.tag <> 'E'
	)
	ORDER BY Dte DESC
	LIMIT 3,6
) t
INNER JOIN t2
	ON t2.id1 = T.id1
WHERE t2.tag <> 'E'
ORDER BY t.Dte DESC

ou encore :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SELECT A.id1, A.dte, X.tag, X.valeur
FROM   t1 AS A
INNER JOIN t2 AS X
    ON X.id1 = A.id1
LEFT JOIN t1 AS B
		INNER JOIN t2 AS Y
			ON Y.id1 = B.id1
			AND Y.tag <> 'E'
	ON B.dte > A.dte
WHERE X.tag <> 'E'
GROUP BY A.id1, A.dte,  X.tag, X.valeur
HAVING COUNT(DISTINCT B.id1) BETWEEN 3 AND 5 
ORDER BY A.dte DESC

Bonjour, et merci d'avoir planché sur mon problème.

Malheureusement aucune des 2 propositions ne retourne le résultat voulu: dans les 2 cas, le nombre d'items uniques sortis est inférieur à celui demandé pour la pagination, et les items sans aucune propriété ne sortent pas du tout.

Pour l'instant, la seule requête qui permette d'obtenir le résultat escompté fait appel à GROUP_CONCAT:

Code :

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

1
2
3
4
5
6
7
SELECT t1.`id1`, t1.`date`, GROUP_CONCAT(t2.`tag`) AS `tags`, GROUP_CONCAT(t2.value) AS `values`
FROM t1
LEFT JOIN t2
	ON (t2.`id1` = t1.`id1` AND t2.`tag` <> 'E')
GROUP BY t1.`id1`
ORDER BY t1.`date` DESC
LIMIT 3, 3

Avec derrière:

Code :

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

1
2
3
4
5
6
7
8
9
10
while ($row = next($rowset))
{
  $result[] = (object) array(
                         'id1' => $row->id1,
                         'date' => $row->date,
                         'property' => (empty($row->tags) ? array() :
                             array_combine(array_map('trim', explode(',', $row->tags)),
                                           array_map('trim', explode(',', $row->values))
                             )));
}

Mais je ne peux pas croire qu'il n'existe pas d'autre(s) solution(s) plus académique(s) ...

D'autres idées ?

[punkoff]

La solution académique du group_concat c'est de ne pas laissez faire la présentation au SGBD mais à la partie applicative.

L'alternative au group_concat c'est la recursion, non supportée par MySql.