SELECT ls.*,
COALESCE(agg.upvotes,0) AS upvotes,
COALESCE(agg.downvotes,0) AS downvotes,
COALESCE(uv.value,0) AS user_vote,
(uss.id IS NOT NULL) AS is_saved
FROM launched_sites ls
LEFT JOIN (
SELECT site_id,
SUM(value = 1) AS upvotes,
SUM(value = -1) AS downvotes
FROM site_votes
GROUP BY site_id
) agg ON agg.site_id = ls.id
LEFT JOIN site_votes uv
ON uv.site_id = ls.id AND uv.user_id = :uid
LEFT JOIN user_saved_sites uss
ON uss.site_id = ls.id AND uss.user_id = :uid2
New Website Launches — Past Week — E-Commerce & Online Stores › Home & Garden › Emergency Preparedness — KE | Website Launches - Website Launches