获取点赞帖子用户信息时COUNT函数仅返回单条结果的技术咨询

Hey there! I see the issue right away—your COUNT() functions are causing the query to collapse all results into a single row because you're using aggregate functions without a GROUP BY clause. Let's break this down and fix it.

Why You're Only Getting One Result #

Aggregate functions like COUNT() tell the database to summarize all matching rows into a single result by default. Since you want to get each user who liked the post along with their stats, you need to group the results by the unique identifier of each user (and any other non-aggregated fields you're selecting).

The Fix: Add GROUP BY and Refine Joins #

Here's how to adjust your query to return every liking user with their details and the follow stats you need:

$sql = "SELECT 
    U.id AS userid, 
    U.name AS NAME, 
    U.username AS username, 
    U.verified AS verified, 
    U.private AS private, 
    COUNT(IFOLLOW.user_id) AS i_follow, 
    COUNT(FR.user_id) AS requested 
FROM posts AS P 
LEFT JOIN likes AS L ON L.post_id = :postid 
LEFT JOIN users AS U ON U.id = L.user_id 
-- Complete the join logic for followers/follow requests
LEFT JOIN followers AS IFOLLOW ON IFOLLOW.user_id = :userid AND IFOLLOW.followed_id = U.id 
LEFT JOIN follow_requests AS FR ON FR.requester_id = :userid AND FR.requested_id = U.id 
-- Ensure we only target the specific post
WHERE P.id = :postid 
-- Group by all non-aggregated user fields to get one row per user
GROUP BY U.id, U.name, U.username, U.verified, U.private";

Key Changes Explained: #

• GROUP BY Clause: This tells the database to group results by each unique user (using U.id as the primary identifier, plus other user fields to comply with strict SQL mode rules). Now COUNT() will calculate values per user instead of across all users.
• Complete Join Conditions: I filled in the missing parts of your followers and follow_requests joins (assuming you're checking if the current user :userid follows or has requested to follow the liking user U.id).
• WHERE Clause: Added a check for P.id = :postid to ensure we're only pulling likes for the exact post you care about (this might have been implicit before, but it's good to make it explicit).

Optional: Use SUM() for Clarity #

If you want to make the follow status logic more readable, you can replace COUNT() with SUM(CASE...)—this makes it obvious we're counting a binary "yes/no" status:

SUM(CASE WHEN IFOLLOW.user_id IS NOT NULL THEN 1 ELSE 0 END) AS i_follow,
SUM(CASE WHEN FR.user_id IS NOT NULL THEN 1 ELSE 0 END) AS requested

This works the same way as COUNT() here, but it's clearer what you're measuring.

Give this adjusted query a try—you should now get a row for every user who liked the specified post, along with their username, verification status, and your follow relationship stats.

内容的提问来源于stack exchange，提问作者Dan