Discussion :

[doogy]

Bonjour !

Je cherche à mettre en place la requête SQL typique des pages de forum, je m'explique.

Nous avons 2 tables :

- topics
* id
* ...

- messages
* id
* timestamp
* topic_id

Je souhaite effectuer la requête SQL qui permet de faire ressortir les topics où viennent d'être posté un message tout en respectant les contraintes suivantes :

- TOUS les topics doivent apparaître UNE fois (y compris les topics où il n'y a pas de message*)
- Le nombre de messages rattaché au topic doit ressortir
- La date du dernier message doit également ressortir

* oui, dans mon cas, un topic n'a pas nécessairement de message. C'est à dire que le message initial du topic n'est pas présent dans la table message.

Je me suis un peu prit la tête et je me demande en fait si c'est possible à faire en une seule requête.

J'ai pensé à LEFT OUTER JOIN pour faire ressortir tous les topics mais je vois difficilement comment respecter les autres contraintes avec cette jointure.

Merci à ceux qui me donneront un petit coup de pouce !

A bientôt

[iks22]

Salut,

je ferai cela:

Code :

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

1
2
3
4
5
6
7
select distinct t1.id,case when (select count (t2.id) from messages t2 where  t1.id=t2.topic_id) is not null
                                           THEN max(t2.timestamp)
                                           ELSE NULL
                                           END date_dernier_message,
(select count (t2.id) from messages t2 where t1.id=t2.topic_id) Nombre_de_messages
from topics t1 left outer join on(messages t2
                                           where t1.id = t2.topic_id)

Peut etre un peu compliqué a cause des messages qu'il n'y a pas toujours,mais elle fait tout ce que tu demandes si je n'ai pas fait d'erreur...

[pacmann]

Salut !

Iks22, c'est horrible ce que tu nous fais là !!!!!!!!!!!!!!
En plus, ça n'a aucune chance de marcher, ne serait-ce que parce qu'il n'y a pas de GROUP BY pour le max...
Franchement, c'est sympa de vouloir aider les gens, mais je te conseille quand même de te documenter un tout petit peu sur le SQL avant...

Donc doogy, la jointure externe : très bien
Après, il te suffit de faire GROUP BY, et les fonctions d'agrégats te permettent de récupérer les informations que tu cherches

Un truc du genre :

Code :

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

1
2
3
4
5
 
SELECT a.id, max(timestamp), count(*)
FROM topics a LEFT OUTER JOIN messages b
  ON a.id = b.topic_id
GROUP BY a.id

[doogy]

Bonjour,

Excellent pacmann, c'est exactement ça ! Je ne pensais pas que la requête pouvait être si "simple". J'ai juste rajouté un petit ORDER BY b.timestamp DESC et le tour est joué.

Merci pacmann

[iks22]

ok, mais moi je suis plus...

max(timestamp), tu n'identifies pas la table de timestamp. Et puis si b.timestamp n'existe pas puisque pas de message, ta requète ne fonctionne pas. Donc dans ce cas l'affichage des topics sans message ne se fait pas.

Je peux me tromper Pacmann, mais si c'est le cas, ne te moques pas, expliques moi...

[pacmann]

Non, je ne me moque pas de toi, mais c'est vrai que j'aurais du t'expliquer dans le détail :

- Je n'identifie pas la table de timestamp, parce que je suppose qu'il n'y a pas de timestamp dans la table topic.
Sinon, oh mon dieu, il faut remplacer par b.timestamp....
- Dans le cas, où il n'y a pas de correspondance, max(timestamp) renvoie NULL.
Après, si à la place de NULL tu préfères une date par défaut, tu fais COALESCE(max(timestamp), TaDateParDefaut)

Les raisons qui me font penser que tu n'es pas tout à fait à l'aise avec les bases du SQL :
- La syntaxe d'une jointure externe, c'est :
TableA LEFT OUTER JOIN Table2 ON ...
Et non "ON (Table2 WHERE ...)"
=> J'ai peut être tort, mais j'ai considéré que c'était plus qu'une faute de frappe

- DISTINCT t1.id, ..., c'est extrêmement laid.
Il ne faut pas que tu t'imagines un traitement itératif ou chaque résultat que tu cherches, tu l'obtiens par sous-requête corrélée...
Mais en termes de jointures, agrégats, ...

Cela dit, ce n'est pas un reproche ni une insulte, simplement une constation.
En apprenant les choses petits à petits, tu progresseras assez vite.

Allez, je te montre ce que ça donne sur l'exemple concret :

Je cherche pour chaque topic tous ces messages :

Code :

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

1
2
3
4
 
SELECT *
FROM topic a JOIN message b
  ON a.id = b.topic_id

Si tu veux aussi les topics sans messages (avec tous les champs de messages à NULL du coup), tu utilises la jointure exterme :

Code :

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

1
2
3
4
 
SELECT *
FROM topic a LEFT OTUER JOIN message b
  ON a.id = b.topic_id

Si tu veux compter le nombre de ligne dans ce résultat pour chaque a.id, il suffit de les regroupper et de compter.
De même, lorsque tu as constitué les groupes, tu peux récupérer la date de message la plus grande, soit la date du dernier message.
Pour cela, il suffit d'utiliser la clause GROUP BY

Code :

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

1
2
3
4
5
 
SELECT a.id, count(*), max(timestamp)
FROM topic a LEFT OTUER JOIN message b
  ON a.id = b.topic_id
GROUP BY a.id

Est-ce que ça te permet d'y voir plus clair ?

[iks22]

Ah là c'est plus clair en effet!
Merci pour ces petites explications! Et à ce propos, sais-tu comment je pourrais progresser pour faire des requètes plus légères et plus efficaces (en PL/SQL)? Oui puis-je apprendre cette facon de faire? (Sans parler bien sûr des cours SQL dans un organisme de formation...)

Merci d'avance

[pacmann]

Avant les spécificités PL / SQL et des aspects transactionnels, lis les tutos SQL : je suppose que SQLPro a du faire des trucs sur ce site (développez.net), et sinon, il doit y en avoir un peu partout sur le net pour acquérir les bases.

En suite, c'est un effort sur la manière de réfléchir qu'il faut faire : voire les problématique d'un point de vue "ensembliste" (j'ai horreur d'utiliser ce mot pour le SQL, mais bon).

Après avoir oublié les trucs qu'on m'a raconté à l'école, je me suis reformé en parcourant les forums...
- Lire les problèmes des autres
- Penser à une solution
- Lire les solutions des autres
- Tirer des conclusions, assimiler, capitaliser...

Une fois que tu auras fait ça, tu pourras t'intéresser de plus près au PL / SQL, pour lequel il existe également une tripotée de tutoriels...

Bon courage

[iks22]

ok, merci je prends note et je vais essayer cette méthode!

Merci et bonne soirée!

[doogy]

Bonsoir !

Je suis content que le topic ai pu servir à quelqu'un d'autre
J'ai cependant une contrainte en plus à ajouter sur la requête SQL qui ne me semble pas évidente du tout. Je reprends :

Nous avons 2 tables :

- topics
* id
* ...

- messages
* id
* timestamp
* username
* topic_id
* ...

Je sais que ce n'est pas très correct de ne pas utiliser de FK pour l'utilisateur qui dépose le message, mais cela correspond à mon besoin actuel.

J'aimerais cette fois ci rajouter dans la requête l'username associé au dernier message posté. Cela est-ce possible en gardant la même base de requête ? Si oui, je veux bien un peu d'aide

Je vous remercie !

A+

[doogy]

Un petit up car je bloque complètement.

Merci

[Az2000]

Ton topic est marqué comme résolu, donc forcément tu as moins de réponses.

Il n'y a malheureusement pas de solution simple à ton problème, mais tu peux trouver ICI une solution.

[doogy]

Merci beaucoup Az2000, j'ai trouvé mon bonheur !
Je donne la requête finale pour les intéressés :

Code :

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

1
2
3
4
5
6
7
8
9
 
SELECT t.id, t.title, m.timestamp, m.count, m.username
FROM (
	SELECT topic_id, username, max(timestamp) AS date, count(*) AS count
	FROM messages
	GROUP BY topic_id
) AS m, topics AS t
WHERE t.id = m.topic_id
ORDER BY m.date DESC , t.title ASC

Encore merci et bonne continuation

[doogy]

Ah non il y a un souci tout compte fait, il ne me retourne pas toutes les occurrences de la table topics mais seulement celles qui ont un message (WHERE t.id = m.topic_id).

Voyez vous une solution ?

[doogy]

Pardon j'ai oublié de mettre en route mon cerveau :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
SELECT t.id, t.title, m.date, m.username, count(*) AS count
FROM topics AS t
LEFT OUTER JOIN (
	SELECT topic_id, username, timestamp
	FROM messages
	ORDER BY timestamp DESC
) AS m ON t.id = m.page_id
GROUP BY t.id
ORDER BY m.date DESC, t.title ASC

A+