dongyingla8668
dongyingla8668
2012-01-05 08:43

MySQL抓取用户的所有帖子和每个帖子的所有喜欢(加入,分组,计数等)

已采纳

I'm building a forum software and am trying to grab all the posts by a particular 'profile', grab all of the profiles that have 'liked' each post ("likers"), followed by a third fetch of the profile data of each profile that has liked each post (profile_name, etc.).

Table 1 - posts
---------------
post_id
profile_id (original poster)
content
...

Table 2 - like
--------------
profile_id
post_id

Table 3 - profiles
------------------
profile_id
profile_name
...

Some rules:

  • Each like is simply a relation of the post_id and profile_id
  • A profile cannot like a post more than once
  • A profile cannot like their own post

I'm stuck on the step where I store all the like profile_ids ("likers") into an array, if using PHP to fetch the data. Here is what I have so far:

SELECT post.*, COUNT(`like`.profile_id) AS like_count 
  FROM post LEFT OUTER JOIN `like` ON post.post_id = `like`.post_id 
    WHERE post.profile_id = "'.$this->profile_id.'" GROUP BY post.post_id

Thanks in advanced. I'll try and update this if I find any mistakes, or if any are pointed out to me.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • dsjlqkbpn029473708 dsjlqkbpn029473708 9年前

    To select all the posts from a particular profile you simply would do the following query:

    SELECT *
    FROM posts
    WHERE profile_id = (the id number)
    

    To grab all the profiles and their data which have liked a given post you would do the following:

    SELECT *
    FROM profiles
    WHERE profile_id IN (
        SELECT profile_id
        FROM like
        WHERE post_id = (the post id number) )
    

    Combining these two queries with IN would allow you to specify a profile and get the profile data for all those who have liked any post on that person's profile. The query would look like this

    SELECT *
    FROM posts
    WHERE profile_id = (the id number)
    

    To grab all the profiles and their data which have liked a given post you would do the following:

    SELECT *
    FROM profiles
    WHERE profile_id IN (
        SELECT profile_id
        FROM like
        WHERE post_id IN (
            SELECT post_id
            FROM posts
            WHERE profile_id = (the id number) )
    

    If you wanted to grab a list of the post information as well as the profile data for all those who liked each post and the posts themselves, you would use a JOIN as follows...

    SELECT *
    FROM profiles JOIN posts ON (profiles.post_id = posts.post_id)
    WHERE ...
    

    ... which would simply aggregate the data from the two tables into one query. You would then pull out the data to treat with PHP as need be.

    If you want to implement rules such as a user cannot like his or her own post, you will have to write that into your php whereby when somebody tries to like a post there is a verification whether or not it is his or her own. You would not want to, for example, allow somebody to like a post, write the like to the database, and then when a page is loaded verify through a query whether or not that person has permission to do so.

    点赞 2 评论 复制链接分享

相关推荐