Quartier Shinsekai

SELECT DATEDIFF(CURRENT_DATE, msg.dateMessage) howManyDays, lastMessage.participants, lieu.lblLieu, sousForum.nomSousForum, sousForum.description, topic.idTopic, topic.nomTopic, topic.descriptionTopic, topic.annee, topic.saison, msg.idComptePrin, msg.idMessage, msg.dateMessage, msg.idUtilisateur, LEFT(msg.texteMessage, 400) as texteMessage, msg.idForum, lastMessage.nbMessages, perso.nomPerso, perso.villagePerso, (SELECT urlAva FROM s_miniAvatars WHERE idPnj=0 AND idPersonnage=msg.idUtilisateur ORDER BY avaPrincipal DESC, idAva ASC LIMIT 0,1) as miniAva FROM ( SELECT MAX(idMessage) idMessage, MAX(dateMessage) dateMessage, COUNT(idMessage) as nbMessages, JSON_ARRAYAGG( DISTINCT JSON_OBJECT( 'idUtilisateur', t_message.idUtilisateur, 'nomPerso', s_personnage.nomPerso, 'villagePerso', s_personnage.villagePerso, 'miniAva', (SELECT urlAva FROM s_miniAvatars WHERE idPnj=0 AND idPersonnage=t_message.idUtilisateur ORDER BY avaPrincipal DESC, idAva ASC LIMIT 0,1) ) ) AS participants FROM t_message LEFT JOIN s_personnage ON t_message.idUtilisateur=s_personnage.idUtilisateur GROUP BY idTopic ) as lastMessage LEFT JOIN t_message msg ON lastMessage.idMessage = msg.idMessage AND lastMessage.dateMessage = msg.dateMessage LEFT JOIN s_personnage perso ON msg.idUtilisateur=perso.idUtilisateur LEFT JOIN t_topic topic ON msg.idTopic=topic.idTopic LEFT JOIN t_sousforumlieu lieu ON topic.idLieu=lieu.idLieu LEFT JOIN t_sousforum sousForum ON msg.idSousForum=sousForum.idSousForum WHERE msg.idSousForum=:idSousforum AND 'gore' NOT IN (SELECT idType FROM t_topicType WHERE idTopic=msg.idTopic) ORDER BY msg.idMessage DESC