php - 如何修改此查询，以便可以按每个帖子的评论数对结果进行排序？

Question

我已经在这个问题上坚持了大约一周了。有一个从数据库中获取帖子的 SQL 查询。我需要添加一个 LEFT JOIN，它允许我根据每个帖子的评论数量对帖子进行排序。从评论最多的帖子到评论最少的帖子。这是我的查询:

SELECT DISTINCT 
    wallposts.p_id,
    wallposts.is_profile_notes,
    wallposts.times_viewed,
    wallposts.columnTimesShared,
    wallposts.marked,wallposts.secure_id,
    wallposts.reshared,wallposts.group_id,
    wallposts.totaluploads,
    wallposts.WallUploadID,
    wallposts.type,
    wallposts.value,
    wallposts.media,
    wallposts.youtube,
    wallposts.post_type,
    wallposts.privacy,
    wallposts.tagedpersons,
    wallposts.with_friends_tagged,
    wallposts.emotion_head,
    wallposts.selected_emotion,
    wallposts.title,
    wallposts.url,
    wallposts.description,
    wallposts.cur_image,
    wallposts.uip,
    wallposts.likes,
    wallposts.userid,
    wallposts.posted_by,
    wallposts.post as postdata,
    wallusers.*, 
    UNIX_TIMESTAMP() - wallposts.date_created AS TimeSpent,
    PosterTable.mem_pic as posterPic,
    PosterTable.gender as posterGender,
    PosterTable.oauth_uid as poster_oauth_uid,
    PosterTable.username as posterUsername, 
    PosterTable.mem_fname as posterFname,
    PosterTable.work as posterWork, 
    PosterTable.mem_lname as posterLname,
    walllikes_track.id as PostLikeFound,
    wallposts.date_created
FROM 
    wallusers,
    wallusers as PosterTable,
    wallposts 
LEFT JOIN
    walllikes_track
ON
    wallposts.p_id = walllikes_track.post_id
AND 
    walllikes_track.member_id = ".$user_id." 
WHERE
    wallusers.active = 1 
AND
    PosterTable.active = 1
AND
    wallposts.group_id IN (".$groups.")
AND
    wallposts.group_id != 0
AND
    PosterTable.mem_id = wallposts.posted_by
AND
    wallposts.marked < ".$this->flagNumber."
AND
    wallusers.mem_id = wallposts.posted_by

“wallposts”是包含我的帖子的表的名称，并且有一个 p_id。 “wallcomments”是包含评论的表的名称，其中有一个名为 post_id 的列，用于将其链接到帖子。请帮助我需要按评论数量对帖子进行排序。我已经尝试过这个，但我的屏幕一片空白，并且我的程序每次都会崩溃:

SELECT DISTINCT 
    wallposts.p_id,
    wallposts.is_profile_notes,
    wallposts.times_viewed,
    wallposts.columnTimesShared,
    wallposts.marked,wallposts.secure_id,
    wallposts.reshared,wallposts.group_id,
    wallposts.totaluploads,
    wallposts.WallUploadID,
    wallposts.type,
    wallposts.value,
    wallposts.media,
    wallposts.youtube,
    wallposts.post_type,
    wallposts.privacy,
    wallposts.tagedpersons,
    wallposts.with_friends_tagged,
    wallposts.emotion_head,
    wallposts.selected_emotion,
    wallposts.title,
    wallposts.url,
    wallposts.description,
    wallposts.cur_image,
    wallposts.uip,
    wallposts.likes,
    wallposts.userid,
    wallposts.posted_by,
    wallposts.post as postdata,
    wallusers.*, 
    UNIX_TIMESTAMP() - wallposts.date_created AS TimeSpent,
    PosterTable.mem_pic as posterPic,
    PosterTable.gender as posterGender,
    PosterTable.oauth_uid as poster_oauth_uid,
    PosterTable.username as posterUsername, 
    PosterTable.mem_fname as posterFname,
    PosterTable.work as posterWork, 
    PosterTable.mem_lname as posterLname,
    walllikes_track.id as PostLikeFound,
    wallposts.date_created,

    p.p_id,
    c.postcount

FROM 
    wallusers,
    wallusers as PosterTable,
    wallposts,
    wallposts as p


INNER JOIN  (
                SELECT
                    post_id,
                    count(*) AS postcount
                FROM
                    wallcomments
                GROUP BY
                    post_id
            ) as c
on
    p.p_id = c.post_id


LEFT JOIN
    walllikes_track
ON
    wallposts.p_id = walllikes_track.post_id
AND 
    walllikes_track.member_id = ".$user_id." 
WHERE
    wallusers.active = 1 
AND
    PosterTable.active = 1
AND
    wallposts.group_id IN (".$groups.")
AND
    wallposts.group_id != 0
AND
    PosterTable.mem_id = wallposts.posted_by
AND
    wallposts.marked < ".$this->flagNumber."
AND
    wallusers.mem_id = wallposts.posted_by

我做错了什么？

Answer 1

尝试反方向做

select a.post_id, ...all the rest ... 
from 
   (select post_id, count(1) as c from  wallcomments group by 1 order by 2 desc) a
left join wallcomments w on w.p_id = a.post_id ...

group by 和 order by 使用列索引/数字而不是列名应该可以正常工作，至少在 MySQL 和 Oracle 中，也许其他也可以。

我想说的是，在您的查询中，您试图“从许多表中获取所有内容”，然后按“另一个表中的某些数据”排序，可能有帮助的是首先关注获取列表post_ids 按您想要的评论数量排序，然后使用其他表中的数据“丰富”每个记录(通过连接 post_id)。

您还可以在评论表上创建一个 View ，仅显示 post_ids、no_of_comments

稍后编辑:在更好地查看您的查询后，在我看来，它有一些不相符的东西......例如，您为什么使用 wallusers 表两次？你似乎以同样的方式链接到它(也许我丢失了一些东西)，无论如何，经过一番摆弄后我想出了这个

SELECT 
    c.*, -- the "count collection" data (also ensures distinct post_id, because it has group by
    p.*, -- posts fields
    u.*, -- user data
    l.* -- likes data
FROM 
    (select post_id, COUNT(1) as comment_count FROM  wallcomments GROUP BY post_id) as c
    LEFT JOIN wallposts as p on p.p_id = c.post_id
    LEFT JOIN wallusers as u on u.mem_id = p.posted_by
    LEFT JOIN walllikes_track as l on l.post_id = p.p_id
WHERE 
    l.member_id = ".$user_id." AND
    p.group_id IN (".$groups.") AND
    p.marked < ".$this->flagNumber."
ORDER BY c.comment_count DESC

您必须对其进行调整才能获得所需的列列表并添加任何其他条件。

作为旁注，请查看“准备语句”，而不是构建和执行原始查询(另请检查 SQL 注入(inject))