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
| SELECT m.id_message, m.date_creation, m.message, m.sharewith, m.nb_votes,
j.id_joueur,
j.nom AS nom_createur,
j.prenom AS prenom_createur,
j.photo AS photo_createur,
j.pseudo AS pseudo_createur,
COUNT(c.id_commentaire) AS nb_reponses,
CASE
WHEN vm.id_joueur IS NOT NULL THEN 1
ELSE 0
END AS vote_du_joueur
FROM les_messages m
INNER JOIN les_joueurs j ON j.id_joueur = m.id_createur
LEFT OUTER JOIN les_commentaires c ON c.id_message = m.id_message
LEFT OUTER JOIN votes_messages vm
ON vm.id_message = m.id_message
AND vm.id_joueur = ".$id_membre_connecte."
WHERE m.id_joueur_mur = ".$id_joueur."
GROUP BY m.id_message, m.date_creation, m.message, m.sharewith, m.nb_votes,
j.nom, j.prenom, j.photo, j.pseudo, vote_du_joueur
UNION
SELECT m2.id_message, m2.date_creation, m2.message, m2.sharewith, m2.nb_votes,
j2.id_joueur, j2.nom, j2.prenom, j2.photo, j2.pseudo,
COUNT(c2.id_commentaire) AS nb_reponses,
CASE
WHEN vm2.id_joueur IS NOT NULL THEN 1
ELSE 0
END AS vote_du_joueur
FROM les_messages m2
INNER JOIN follow f ON f.flw_id_following = m2.id_createur
INNER JOIN les_joueurs j2 ON j2.id_joueur = f.flw_id_following
LEFT OUTER JOIN les_commentaires c2 ON c2.id_message = m2.id_message
LEFT OUTER JOIN votes_messages vm2
ON vm2.id_message = m2.id_message
AND vm2.id_joueur = ".$id_membre_connecte."
WHERE m2.id_joueur_mur = f.flw_id_following
AND f.flw_id_follower = ".$id_joueur."
GROUP BY m2.id_message, m2.date_creation, m2.message, m2.sharewith, m2.nb_votes,
j2.nom, j2.prenom, j2.photo, j2.pseudo, vote_du_joueur
ORDER BY date_creation DESC |
Partager