[SQL] Need help !

Ahava · 21 Mai 2007

Bonjour,

Bon je suis peut-etre trop fatigué, ou je sais pas, mais je m'en sors pas avec une requete. C'est pour l'affichage des sujets dans un forum !

Les tables :

Code:

topics ( [u]id[/] , name , description , position , category ) 
posts ( [u]id[/] , id_user , id_topic , body , title , date , views )
replies ( [u]id[/] , id_user , id_post _ body , date)

Ce que je veux donc : Récupérer la liste des topics d'une catégorie (les sujets d'un forum), par ordre de date de post si y a aucune réponse au post, ou par ordre de dernier reply du post. Et c'est cette condition qui me pose un énorme problème, le tout mixé dans une seul requete.

Ce que j'ai fait jusqu'à présent, qui est boiteux :

Code:

SELECT p.id , p.id_user , p.id_topic , p.body , p.title , p.priority , p.views , p.date , r.date as replyDate
FROM topics AS t
JOIN  posts AS p ON p.id_topic = t.id
LEFT JOIN replies AS r ON r.id_post = p.id
WHERE p.id_topic = 23
AND (

r.date IS NULL OR r.date = ( 
 SELECT r.date 
 FROM csdw_forum_replies AS r 
 JOIN csdw_users AS u ON r.id_user = u.id 
 WHERE r.id_post = p.id
 ORDER BY r.date DESC
 LIMIT 1
	)
)

Donc ca me donne ce résultat (l'image en fichier joint). J'ai tenté un order by r.date , p.date mais ca me donne pas le résultat escompté. Je pense être en fait sur une mauvaise piste sans arriver à trouver la bonne voie

Merci pour votre temps

k o D · 21 Mai 2007

Déjà, je ne sais pas si tu comptes faire un forum distribuable, mais si je ne me trompe pas, en dessous de la version 5 de MySQL, il ne supporte pas les Sub Select.

Je vais essayer de répondre à ta question, une petite minute :-D

Ahava · 21 Mai 2007

Non ca sera pas distributable du tout... merci

eGm_ · 21 Mai 2007

truc de noob : tu crées une vue ca ira plus vite non ?? XD

k o D · 21 Mai 2007

Ahava a dit:
Non ca sera pas distributable du tout... merci

Peux-tu m'envoyer ses 3 tables avec du contenu, je vais tester ma requête en local :-D

Je ne sais pas si elle va fonctionner, faut voir comme SQL va réagir en fait

Bingo · 21 Mai 2007

Tu peux essayer :

SELECT p.id , p.id_user , p.id_topic , p.body , p.title , p.priority , p.views , COALESCE(r.date, p.date) as lastdate
FROM topics AS t
INNER JOIN posts AS p ON p.id_topic = t.id
LEFT JOIN replies AS r ON r.id_post = p.id
ORDER BY lastdate;

PS : hors sujet : C'est bizarre d'avoir une table topics, une table posts et une table replies. Des replies, ce sont des posts après tout ! J'aurais fait une table topics et une table posts.

k o D · 21 Mai 2007

Bingo a dit:
Tu peux essayer :

SELECT p.id , p.id_user , p.id_topic , p.body , p.title , p.priority , p.views , COALESCE(r.date, p.date) as lastdate
FROM topics AS t
INNER JOIN posts AS p ON p.id_topic = t.id
LEFT JOIN replies AS r ON r.id_post = p.id
ORDER BY lastdate;

PS : hors sujet : C'est bizarre d'avoir une table topics, une table posts et une table replies. Des replies, ce sont des posts après tout ! J'aurais fait une table topics et une table posts.

Bah je pense qu'il t'a donné la solution avec COALESCE.
Sinon essaye aussi sans... c'est à dire que tu tries de cette manière:

ORDER BY R.date, P.date DESC

Pour moi, çà devrait passer...

EDIT: je ne trie plus sur l'id... j'avais oublié la fonctionnalité principale d'un forum... l'affichage des posts avec les réponses les plus récentes :-'

Ahava · 21 Mai 2007

Une vue, peut-etre, je sais pas ce qui est le mieux... J'avais fait une découpe tres précise, et ca me semble un peu redondant d'avoir tellement de champs vides lorsqu'un reply est posté dans une table post... Je me tâte quoi. Peut-etre qu'une seule table est assez, mais je sais pas ce qui est mieux entre une auto-jointure ou une jointure avec une autre table...

J'ai mis les trois tables là :

:arrow: www.ouep.be/files/sqlrequest.txt

Bingo · 21 Mai 2007

Ahava a dit:
Une vue, peut-etre, je sais pas ce qui est le mieux... J'avais fait une découpe tres précise, et ca me semble un peu redondant d'avoir tellement de champs vides lorsqu'un reply est posté dans une table post... Je me tâte quoi. Peut-etre qu'une seule table est assez, mais je sais pas ce qui est mieux entre une auto-jointure ou une jointure avec une autre table...

A mon avis, les champs relatif au topic de la table "posts" devraient se trouver dans la table "topics" (title et views).
Du coup tes tables "posts" et "replies" sont identiques, ce qui me semble logique. Il faudrait peut-être rajouter un champ "order" qui indique l'ordre du post dans le topic (1 pour le premier post, etc...).

Sinon la requête fonctionne ? La solution de KoD avec les deux champs dans le order by doit fonctionner aussi.
L'avantage du COALESCE c'est que tu as la date du post le plus récent dans ton SELECT, du coup tu peux l'afficher sans requête supplémentaire.

k o D · 21 Mai 2007

Oui, ce que j'obtiens me semble cohérent...

Code:

SELECT T.id AS topicID, P.id_user AS userPost, R.id_user AS userReply, P.id, P.body, P.title, P.priority, P.views, P.date, R.date AS replyDate
FROM topics AS T, posts AS P
LEFT JOIN replies AS R ON ( R.id_post = P.id )
WHERE T.id = P.id_topic
AND P.id_topic =23
ORDER BY R.date, P.date DESC

çà me semble correct :

Ahava · 21 Mai 2007

la requete fonctionne bien, j'ai l'impression

Merci beaucoup, je connaissais pas ce COALESCE

Ahava · 21 Mai 2007

Kod, ta solution marche pas ca si l'on poste un nouveau reply dans un post, il est mis en premier oui, mais dans le paquet de ceux qui ont un reply. Vu le order by R.date, il trie d'abord par NULL - NON NULL et dans les non NULL, par ordre ASC...

Donc un nouveau reply dans un post ou y en a reste dans le paquet des NON NULL, donc tous les nouveaux posts sont regroupés et tous les "répondus" en dessous... J'avais testé ca, je pensais aussi que ca marcherait, j'ai tourné ces order by r.date, p.date dans tous les sens...

Merci à tous, la solution COALESCE fonctionne parfaitement

/me content !

k o D · 21 Mai 2007

Ahava a dit:
Kod, ta solution marche pas ca si l'on poste un nouveau reply dans un post, il est mis en premier oui, mais dans le paquet de ceux qui ont un reply. Vu le order by R.date, il trie d'abord par NULL - NON NULL et dans les non NULL, par ordre ASC...

Donc un nouveau reply dans un post ou y en a reste dans le paquet des NON NULL, donc tous les nouveaux posts sont regroupés et tous les "répondus" en dessous... J'avais testé ca, je pensais aussi que ca marcherait, j'ai tourné ces order by r.date, p.date dans tous les sens...

Merci à tous, la solution COALESCE fonctionne parfaitement

/me content !

Effectivement

Ahava · 21 Mai 2007

y avait encore un bug !

En fait, il faut que je recherche quand meme le dernier reply pour le comparer via ce magnifique opérateur COALESCE que j'adore, à la date du post

Donc la requete finale est :

Code:

SELECT p.id , p.id_user , p.id_topic , p.body , p.title , p.priority , p.views , p.date as pDate , r.date as rDate ,  COALESCE(r.date, p.date) as lastdate
FROM topics AS t
JOIN posts AS p ON p.id_topic = t.id
LEFT JOIN replies AS r ON r.id_post = p.id
WHERE p.id_topic = 23
AND (

r.date IS NULL OR r.date = ( 
 SELECT r.date 
 FROM csdw_forum_replies AS r 
 JOIN csdw_users AS u ON r.id_user = u.id 
 WHERE r.id_post = p.id
 ORDER BY r.date DESC
 LIMIT 1
	)
) order by lastDate DESC

Si vous avez des idées d'optimisation, je suis preneur

Je ferai peut-etre une seule table, ca va bien tout simplier quand meme... Mais pour le moment je dois faire comme ca...

Merci encore à tous

eGm_ · 21 Mai 2007

créer une vue

Bingo · 21 Mai 2007

Ahava a dit:
Si vous avez des idées d'optimisation, je suis preneur

Je ne suis pas sûr de bien comprendre la structure de ta DB, mais déjà tu inclus ta table topics dans ton FROM alors que tu ne l'utilises pas.
Ensuite je ne comprends pas ce que fais ta sous-requête (j'ai pas vraiment cherché à comprendre non plus), mais si tu veux seulement la date du post ou la date maximum d'un reply si il y en a un, tu peux essayer :

SELECT p.id , p.id_user , p.id_topic , p.body , p.title , p.priority , p.views , MAX(COALESCE(r.date, p.date)) as lastdate
FROM posts AS p
LEFT JOIN (SELECT id_post, max(date) FROM replies GROUP BY id_post) AS r
ON r.id_post = p.id
ORDER BY lastdate;

Bingo · 21 Mai 2007

eGm_ a dit:
créer une vue

Ouais, mais pour créer une vue il faut commencer par créer la requête qui va définir la vue... :]

SkYlEsS · 22 Mai 2007

Au fait, que voulez-vous faire par créer une vue ?

Ahava · 22 Mai 2007

Une vue est une table qui répond à un ordre sql sur ta base...

Comme ca quand tu dois travailler dessus, tu l'appelle comme une simple table ! Ca permet aussi de cacher les vrais tables aux users de la db par exemple...

[SQL] Need help !

Ahava

Revenant

k o D

Elite

Ahava

Revenant

eGm_

Gibon Blasé

k o D

Elite

Bingo

Beer Addict

k o D

Elite

Ahava

Revenant

Bingo

Beer Addict

k o D

Elite

Ahava

Revenant

Ahava

Revenant

k o D

Elite

Ahava

Revenant

eGm_

Gibon Blasé

Bingo

Beer Addict

Bingo

Beer Addict

SkYlEsS

Elite

Ahava

Revenant

Autres sujets récents