Si vous avez les deux tables PostgreSQL suivantes :

Tableau des messages:

postid | title | author | created

Tableau des votes:

postid | username | vote

Où vote est égal à 1 si l'utilisateur a voté pour le post, 0 si l'utilisateur n'a pas voté et -1 si l'utilisateur a voté contre le post.

Je veux recevoir maintenant pour chaque message son titre, son auteur, sa date de création, la somme de tous les votes et le vote de l'utilisateur actuellement connecté. J'ai écrit une requête pour tout recevoir sauf le vote de l'utilisateur actuel comme ceci :

SELECT post.postID as postID, post.title as title, post.author as author,
       COALESCE(sum(votes.vote), 0) as voteCount, post.created as created
       FROM post LEFT JOIN votes ON post.postID = votes.postID 
       GROUP BY post.postID ORDER BY voteCount DESC

J'ai essayé de récupérer le userVote actuel en exécutant une sous-requête comme

(SELECT vote FROM votes WHERE postID = post.postID AND username = :username) as userVote 

Cependant, cela ne semble pas fonctionner et je n'arrive pas à comprendre pourquoi et comment le réparer. Toute aide serait très appréciée :)

1
Konstantin 15 oct. 2020 à 12:46

1 réponse

Meilleure réponse

Vous y êtes presque avec votre requête. Utilisez simplement la clause filter pour obtenir le nombre de votes de l'utilisateur actuel

SELECT 
    post.postID as postID, 
    post.title as title, 
    post.author as author,
    post.created as created,
    COALESCE(sum(votes.vote), 0) as voteCount, 
    COALESCE(sum(votes.vote) filter (where votes.username= 'username'), 0) as userVote  -- in '' just provide username for current login user
FROM post 
LEFT JOIN votes ON post.postID = votes.postID 
GROUP BY 1,2,3,4 
ORDER BY 5 DESC

Une autre façon consiste à utiliser une autre jointure gauche comme ci-dessous :

SELECT 
    p.postID as postID, 
    p.title as title, 
    p.author as author,
    p.created as created,
    COALESCE(sum(v1.vote), 0) as voteCount, 
    COALESCE(v2.vote , 0) as userVote  -- in '' just provide username for current login user
FROM post p
LEFT JOIN votes v1 ON p.postID = v1.postID 
LEFT JOIN votes v2 on p.postID = v2.postID and v2.username='username'
GROUP BY 1,2,3,4,v2.vote
ORDER BY 5 DESC

DEMO

1
Akhilesh Mishra 15 oct. 2020 à 10:27