Discussion :

[Yatta]

J'ai deux tables

'people' (avec id, nom et compagnie)
'event' (avec about_id (correspondant aux id des 'people'), date, type, ...)

Je veux faire deux options de tri :

- Une requête qui select tous les 'people', en les triant par DATE de l''event' le plus récent qui leur soit associé, sachant qu'il peut évidemment y avoir plusieurs 'event' pour un 'people'.

- Une requête qui select tous les 'people', en les triant par TYPE de l''event' le plus récent qui leur soit associé.

En gros il faudrait que je fasse un select du genre
"... ORDER BY (le event.date du plus récent des event dont event.about_id=people.id)"
et
"... ORDER BY (le event.type du plus récent des event dont event.about_id=people.id)"

en bref ... help ?

[Waldar]

Qu'avez-vous commencé à écrire ?

[CinePhil]

Une requête peut être composée des clauses :
SELECT
FROM
JOIN
WHERE
GROUP BY
HAVING
avant d'arriver à ORDER BY.

Autrement dit, tu vas un peu vite dans ta réflexion !

- Une requête qui select tous les 'people', en les triant par DATE de l''event' le plus récent qui leur soit associé, sachant qu'il peut évidemment y avoir plusieurs 'event' pour un 'people'.

On cherche donc l'event le plus récent (qui a la date la plus grande) pour chaque people :

Code :

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

1
2
3
4
5
SELECT p.id, p.nom, MAX(e.laDate) AS DateDernierEvent
FROM people AS p
INNER JOIN event AS e ON p.id = e.about_id
GROUP BY p.id
ORDER BY MAX(e.laDate) DESC

Maintenant qu'on a la date de l'event le plus récent, on peut l'utiliser :

Code :

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

1
2
3
4
5
6
7
8
9
SELECT t.nom, t.DateDernierEvent, e1.type
FROM event AS e1
INNER JOIN (
  SELECT p.id, p.nom, MAX(e.laDate) AS DateDernierEvent
  FROM people AS p
  INNER JOIN event AS e ON p.id = e.about_id
  GROUP BY p.id
) AS t ON e1.about_id = t.id
WHERE e1.laDate = t.DateDernierEvent

[pacmann]

Salut !

Attention à soit ajouter p.nom dans le GROUP BY, soit prendre max(p.nom) dans le SELECT.

[Yatta]

Merci beaucoup à vous, surtout CinePhil pour le code et l'explication ! \o/

Je vais mettre ça en place, et étudier un peu ces MAX() et INNER JOIN que je ne connais pas (je ne connais vraiment que les trucs basiques en SQL pour l'instant ...)

Super réponse, et rapide en plus

[Yatta]

Ca marche bien pour le tri par date, mais j'en remet une couche pour le tri par nature d'event.

Voilà ce que j'ai fait en me basant sur l'exemple précédemment donné :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
$req  = "SELECT joint.id, joint.LastOne, logs.status ";
$req .= "FROM suit_logs AS logs ";
$req .= "INNER JOIN (";
$req .= "SELECT p.id, p.nickname, jlog.status AS status, MAX(jlog.date_add) AS LastOne ";
$req .= "FROM suit_persons AS p ";
$req .= "INNER JOIN suit_logs AS jlog ON p.id = jlog.about_id ";
$req .= "GROUP BY p.id";
$req .= ") AS joint ON (logs.about_id = joint.id AND logs.date_add = joint.LastOne) ";
$req .= "WHERE logs.date_add = joint.LastOne ORDER BY joint.status";

Le tri par suit_logs.status fonctionne, mais se base sur le status le plus ancien attribué à une personne (ou sans doute le premier status trouvé dans la liste attribué à cette personne). Je voudrais donc qu'il se base sur le status le plus récent.

Le "MAX(jlog.date_add)" / "logs.date_add = joint.LastOne" n'est pas censé sélectionner seulement le log le plus récent ?

[estofilo]

Une remarque sur le code: à supposer que ce soit du php, mais ça existe aussi en Perl, la syntaxe dite "here-doc" est beaucoup plus pratique pour mettre en chaîne de caractères des requêtes multi-lignes:

Code :

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

1
2
3
4
5
6
7
8
 
$str = <<<REQUETE
SELECT p.id, p.nom, MAX(e.laDate) AS DateDernierEvent
FROM people AS p
INNER JOIN event AS e ON p.id = e.about_id
GROUP BY p.id
ORDER BY MAX(e.laDate) DESC
REQUETE;

Ca permet non seulement de copier-coller directement mais aussi de pouvoir intégrer directement des guillemets et apostrophes sans avoir à les échapper.

[Yatta]

En effet, ça semble plus pratique. Mais cette requête SQL (et sa jumelle) sont les seules de mon code qui aient besoin de plus d'une ligne ...

Enfin je suis toujours coincé avec cette histoire de tri.
J'ai beau faire divers essai, je n'arrive pas à faire en sorte de trier les 'persons' en fonction du 'status' de l''event' le plus récent qui leur corresponde.
(Au moins j'arrive à le formuler en français, c'est déjà ça)

[estofilo]

Dans cette sous-requête:

Code :

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

1
2
3
4
5
 
$req .= "SELECT p.id, p.nickname, jlog.status AS status, MAX(jlog.date_add) AS LastOne ";
$req .= "FROM suit_persons AS p ";
$req .= "INNER JOIN suit_logs AS jlog ON p.id = jlog.about_id ";
$req .= "GROUP BY p.id";

pour ma part je ne saisis pas pourquoi jlog.status n'est pas associé à une fonction de regroupement alors que jlog.date_add l'est. En l'état cette requête est acceptée par le moteur SQL??
Et question connexe, au niveau supérieur pourquoi trier sur jlog.status alors qu'a priori ça devrait plutôt être ORDER BY logs.status, non?

[Yatta]

Estofilo > Merci pour ta requête simplifiée pour la date. Pour le reste, moi non plus, je ne comprends pas ! J'ai juste essayé d'adapter la requête que m'avait donné CinePhil pour le tri par date en une requête sur le type d'event, et comme tu vois, essayer de modifier un truc que je comprends pas c'est pas une bonne idée.

Voilà où j'en suis pour ma requête visant à trier les "peoples" en fonction du dernier "events" en date qui leur soit associé.

Code :

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

1
2
3
4
5
6
SELECT *, p.id AS pid FROM people AS p
INNER JOIN (
     SELECT * FROM events AS s GROUP BY s.about_id 
     HAVING s.date_add=MAX(s.date_add)
) AS j ON j.about_id = p.id
ORDER BY j.status

Sur le coup j'ai cru que ça marchait, mais en fait il ne me sort que les "peoples" qui n'ont qu'un seul "events" associé. Si j'enlève la clause HAVING je retombe sur une liste de people effectivement triés par leurs "events" associé, mais par le plus ancien (ou sans doute le premier correspondant trouvé dans la bdd).

[CinePhil]

Ca marche bien pour le tri par date, mais j'en remet une couche pour le tri par nature d'event.

Voilà ce que j'ai fait en me basant sur l'exemple précédemment donné :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
$req  = "SELECT joint.id, joint.LastOne, logs.status ";
$req .= "FROM suit_logs AS logs ";
$req .= "INNER JOIN (";
$req .= "SELECT p.id, p.nickname, jlog.status AS status, MAX(jlog.date_add) AS LastOne ";
$req .= "FROM suit_persons AS p ";
$req .= "INNER JOIN suit_logs AS jlog ON p.id = jlog.about_id ";
$req .= "GROUP BY p.id";
$req .= ") AS joint ON (logs.about_id = joint.id AND logs.date_add = joint.LastOne) ";
$req .= "WHERE logs.date_add = joint.LastOne ORDER BY joint.status";

Le tri par suit_logs.status fonctionne, mais se base sur le status le plus ancien attribué à une personne (ou sans doute le premier status trouvé dans la liste attribué à cette personne). Je voudrais donc qu'il se base sur le status le plus récent.

Le "MAX(jlog.date_add)" / "logs.date_add = joint.LastOne" n'est pas censé sélectionner seulement le log le plus récent ?

La sous-requête ne peut pas fonctionner correctement :

Code :

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

1
2
3
4
  SELECT p.id, p.nickname, jlog.status AS status, MAX(jlog.date_add) AS LastOne
  FROM suit_persons AS p
  INNER JOIN suit_logs AS jlog ON p.id = jlog.about_id
  GROUP BY p.id

Comme l'a signalé PACMAN, dans le GROUP BY doivent figurer toutes les colonnes qui ne font pas l'objet d'une fonction de regroupement (MAX, MIN, AVG, COUNT, SUM).
Avec ma requête, GROUP BY p.id était suffisant parce que p.nom est directement dépendant de p.id mais dans la tienne, jlog.status donnera la première valeur trouvée pour cette colonne et ce ne sera pas forcément celle que tu cherches à avoir.
A noter que MySQL est tolérant sur l'absence de colonnes dans le GROUP BY mais que d'autres SGBD le refuseraient carrément !

Si je comprends bien ta requête et que je la rapporche de ton besoin, jlog.status correspondrait à mon e1.type ?
Alors ta requête est inutilement compliquée ! Il suffit de transformer ma requête et de faire le tri sur la bonne colonne :

Code :

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

1
2
3
4
5
6
7
8
9
SELECT joint.id, joint.lastone, logs.status
FROM suit_logs AS logs
INNER JOIN (
  SELECT p.id, p.nickname, MAX(jlog.date_add) AS LastOne
  FROM suit_persons AS p
  INNER JOIN suit_logs AS jlog ON p.id = jlog.about_id
  GROUP BY p.id, p.nickname
) AS joint ON logs.date_add = joint.LastOne
ORDER BY logs.status

Nota :
Comme tu le vois dans le code ci-dessus, STATUS est uin mot réservé du SQL et ne devrait pas être utilisé pour nommer une colonne.

[Yatta]

Ouaip Cinephil, la requête que tu m'as donnée marche très bien, merci bcp !

J'essaye de ne pas copier bêtement mais j'avoue qu'avec SQL j'ai du mal.

Voici mon interprétation en français de la requête que tu m'as donnée :

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
 
SELECT joint.id, joint.lastone, logs.STATUS
FROM suit_logs AS logs
// on veut récupérer les champs "id" et "lastone" 
// qu'on va obtenir dans le résultat de l'inner join "joint"
// et le champ "status" de la table logs
 
 
INNER JOIN (
  SELECT p.id, p.nickname, MAX(jlog.date_add) AS LastOne
  FROM suit_persons AS p
  INNER JOIN suit_logs AS jlog ON p.id = jlog.about_id
  GROUP BY p.id, p.nickname
) 
// On prend les suit persons, et on y joint les logs 
// sur condition que l'id corresponde, et on groupe
// la liste obtenue sur id et nickname, seulement
// parceque c'est requis par la syntaxe de GROUP (?).
// On récupère la valeur max des date_add du groupe.
 
AS joint ON logs.date_add = joint.LastOne
// Le JOIN a pour effet de filtrer les lignes obtenues
// par la première requête 'FROM suit_logs' ainsi :
// Ne garder que les lignes où la date = la dernière date
// qu'on a obtenue par le join
 
ORDER BY logs.STATUS
// On trie en fonction du status

Bon en écrivant tout ça à plat, j'ai enfin compris. Je crois.
En fait je crois que je me plantais dans ma démarche principalement sur le fait que j'essayais de prendre les personnes et trouver leur dernier event, puis trier le tout, au lieu de trier directement les events.

Mais tout de même ça me semble un peu compliqué pour une requête finalement assez simple : "Trier MACHINS en fonction du dernier TRUC associé".

En fait est-ce que je ne peux pas faire un truc comme :

Code :

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

1
2
SELECT e.date_add, e.status, e.about_id FROM suit_logs AS e 
SORT BY e.about_id, e.date_add

ce qui me donne grosso modo :

Code :

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

1
2
3
4
5
6
7
8
9
10
about_id | date_add | status
=======================
1          15-10-09   cool
1          14-10-09   pas cool
1          10-10-09   new
2          13-10-09   pas cool
2          05-10-09   pas cool
2          20-09-09   new
3          15-10-09   cool
3          13-10-09   new

Puis filtrer cette liste pour n'obtenir que la première ligne pour chaque about_id :

Code :

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

1
2
3
4
5
about_id | date_add | status
=======================
1          15-10-09   cool
2          13-10-09   pas cool
3          15-10-09   cool

Et enfin trier ce qu'il reste sur 'status' :

Code :

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

1
2
3
4
5
about_id | date_add | status
=======================
1          15-10-09   cool
3          15-10-09   cool
2          13-10-09   pas cool

C'est faisable en une requête sql ?

[CinePhil]

Tu peux effectivement faire le premier truc, c'est à dire récupérer la liste des suit_logs triée par about_id puis par date_add. Sauf que ce n'est pas SORT BY mais ORDER BY.

Mais dans ta table des suit_logs, il me semble que les about_id ne sont pas en un seul exemplaire ? C'est d'ailleurs pour ça que tu cherches à récupérer le MAX(date_add) ! Dès lors, il y a aussi plusieurs status et pour récupérer celui qui correspond à la MAX(date_add), on ne peut faire autrement que d'utiliser une sous requête en une jointure.

Si seul about_id te suffit, plus besoin de jointure avec autre chose mais il y aura au moins une auto-jointure.
Soit ça :

Code :

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

1
2
3
4
5
6
7
8
9
SELECT s2.about_id, s1.status, s2.DateMax
FROM suit_logs AS s1
INNER JOIN (
  SELECT about_id, MAX(date_add) AS DateMax
  FROM suit_logs
  GROUP BY e.date_add, e.status
) AS s2 ON s1.about_id = s2.about_id
WHERE s1.date_add = s2.DateMax
ORDER BY s1.status

Soit ça (mais vu l'heure je ne suis pas sûr de moi, à tester) :

Code :

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

1
2
3
4
5
6
SELECT s1.about_id, s2.status, s2.date_add
FROM suit_logs AS s1
INNER JOIN suit_logs AS s2 ON s1.about_id = s2.about_id
GROUP BY s1.about_id
HAVING s2.date_add = MAX(s1.date_add)
ORDER BY s2.status

[Yatta]

CinePhil, j'ai essayé tes deux requêtes :

La première ne marche pas, il y a une référence à "e" qui n'est pas définie, et s'il s'agit du suit_logs ouvert dans l'inner join, ça fonctionne mais le résultat retombe sur le problème initial : les entrées sont triées par le 'status' du premier 'logs' trouvé et non du dernier en date.

La seconde fonctionne mais ne renvoie que les résultats pour les logs uniques (si plusieurs logs avec le même about_id existe, ils sont tous filtrés).

Mais bon, j'ai maintenant quelque chose qui marche, et la réponse à toutes mes questions ! Donc, thread "résolu" !

Merci tlm !