Search code examples
phpmysqljoingroup-byrelational

MySQL grabbing all posts by a user and all the likes for each post (joining, grouping, counting, etc.)

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.

Solution

  • 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.