Jack Jack - 1 month ago 11
MySQL Question

MYSQL Query - Include posts of people I am following in my current query

I am trying to get the posts that belong to the user's I follow in my current query.

At the moment I am getting: Current user's posts - like count for each post & and comments.

I need to have this result though: Current user's posts - People I am following post's - like count for each post & comments.

The final result is like most social network homepages do. They get your posts and the posts of the people you are following / or your friends posts.

This is my current query:

SELECT
Posts.id
, Posts.uuid
, max(case when rcom.row_number = 1 then rcom.comment end) Comment_one
, max(case when rcom.row_number = 2 then rcom.comment end) Comment_two
, Posts.caption
, Posts.path
, Posts.`date`
, USERS.id
, USERS.username
, USERS.fullname
, USERS.profileImage
, COALESCE(A.LikeCNT, 0) num_likes
FROM Posts
INNER JOIN USERS ON Posts.id = 145
AND USERS.id = 145
LEFT JOIN (
SELECT
COUNT(A.uuidPost) LikeCNT
, A.UUIDPost
FROM Activity A
WHERE type = 'like'
GROUP BY
A.UUIDPOST
) A ON A.UUIDPost = Posts.uuid
LEFT JOIN (
SELECT
@row_num := IF(@prev_value=UUIDPOST,@row_num+1,1) as row_number
, comment
, uuidPost
, `date`
, @prev_value := UUIDPOST
FROM Activity
CROSS JOIN ( SELECT @row_num := 1, @prev_value := '' collate utf8_unicode_ci ) xy
WHERE type = 'comment'
ORDER BY
uuidPost
, `date` DESC
) rcom ON rcom.uuidPost = Posts.UUID
AND rcom.row_number <= 2
GROUP BY
Posts.id
, Posts.uuid
, Posts.caption
, Posts.path
, Posts.`date`
, USERS.id
, USERS.username
, USERS.fullname
, USERS.profileImage
, COALESCE(A.LikeCNT, 0)

ORDER BY date DESC

;


How are my followers stored?

I store my followers in the "Activity" table like so: id(currentUser) - idOtherUser(Person I am following) - type(which is equal to "Follow").

That is it. Here is a sql fiddle with some sample data.

Any further questions please let me know.

Answer

This could be what you are looking for.

I assumed every user has to hit the follow button himself. AlsoThis is written from the perspective from user 145, you have to change the WHERE-clauses. In your sample there is no relation for user 145 following user 123, I added it to test the query.

I removed the Users.id from the select list, as it is the same as Posts.id anyways.

Some sample data for the like count would have been nice, don't know how to fill your tables.

SELECT
    *
FROM
    (
        SELECT
            Posts.id,
            Posts.uuid,
            max(
                CASE
                WHEN rcom.row_number = 1 THEN
                    rcom.commentText
                END
            ) Comment_one,
max(
    CASE
    WHEN rcom.row_number = 1 THEN
        rcom.commentUserName
    END
) Comment_one_user,
            max(
                CASE
                WHEN rcom.row_number = 2 THEN
                    rcom.commentText
                END
            ) Comment_two,
max(
    CASE
    WHEN rcom.row_number = 2 THEN
        rcom.commentUserName
    END
) Comment_two_user,
            Posts.`date`,
            USERS.username,
            USERS.profileImage,
            COALESCE (A.LikeCNT, 0) num_likes
        FROM
            Posts
        INNER JOIN USERS ON Posts.id = USERS.id 
        LEFT JOIN (
            SELECT
                COUNT(A.uuidPost) LikeCNT,
                A.UUIDPost
            FROM
                Activity A
            WHERE
                type = 'like'
            GROUP BY
                A.UUIDPOST
        ) A ON A.UUIDPost = Posts.uuid
            LEFT JOIN (
                SELECT
                    @row_num :=
                IF (
                    @prev_value = UUIDPOST ,@row_num + 1,
                    1
                ) AS row_number,
                commentText,
                uuidPost,
Activity.`date`,
                @prev_value := UUIDPOST,
USERS.id AS commentUserId,
USERS.username AS commentUserName
            FROM
                Activity
                        CROSS JOIN (
                                SELECT
                                        @row_num := 1,
                                        @prev_value := '' COLLATE utf8_unicode_ci
                                ) xy
INNER JOIN USERS ON USERS.id = Activity.id
                        WHERE
                                type = 'comment'
                        ORDER BY
                                uuidPost,
                                `date` DESC
            ) rcom ON rcom.uuidPost = Posts.UUID AND rcom.row_number <= 2

WHERE Posts.id = 145

        GROUP BY
            Posts.id,
            Posts.uuid,
            Posts.`date`,
            USERS.username,
            USERS.profileImage,
            COALESCE (A.LikeCNT, 0)
        UNION
            SELECT
                Posts.id,
                Posts.uuid,
            max(
                CASE
                WHEN rcom.row_number = 1 THEN
                    rcom.commentText
                END
            ) Comment_one,
max(
    CASE
    WHEN rcom.row_number = 1 THEN
        rcom.commentUserName
    END
) Comment_one_user,
            max(
                CASE
                WHEN rcom.row_number = 2 THEN
                    rcom.commentText
                END
            ) Comment_two,
max(
    CASE
    WHEN rcom.row_number = 2 THEN
        rcom.commentUserName
    END
) Comment_two_user,
                Posts.`date`,
                USERS.username,
                USERS.profileImage,
                COALESCE (A.LikeCNT, 0) num_likes
            FROM
                Posts
            INNER JOIN Activity ON Activity.type = 'Follow' AND Posts.id = Activity.IdOtherUser
            INNER JOIN USERS ON Activity.IdOtherUser = USERS.id
            LEFT JOIN (
                SELECT
                    COUNT(A.uuidPost) LikeCNT,
                    A.UUIDPost
                FROM
                    Activity A
                WHERE
                    type = 'like'
                GROUP BY
                    A.UUIDPOST
            ) A ON A.UUIDPost = Posts.uuid
            LEFT JOIN (
                SELECT
                    @row_num :=
                IF (
                    @prev_value = UUIDPOST ,@row_num + 1,
                    1
                ) AS row_number,
                commentText,
                uuidPost,
Activity.`date`,
                @prev_value := UUIDPOST,
USERS.id AS commentUserId,
USERS.username AS commentUserName
            FROM
                Activity
                        CROSS JOIN (
                                SELECT
                                        @row_num := 1,
                                        @prev_value := '' COLLATE utf8_unicode_ci
                                ) xy
INNER JOIN USERS ON USERS.id = Activity.id
                        WHERE
                                type = 'comment'
                        ORDER BY
                                uuidPost,
                                `date` DESC
            ) rcom ON rcom.uuidPost = Posts.UUID AND rcom.row_number <= 2

WHERE Activity.id = 145

            GROUP BY
                Posts.id,
                Posts.uuid,
                Posts.`date`,
                USERS.username,
                USERS.profileImage,
                COALESCE (A.LikeCNT, 0)
    ) AS posts
ORDER BY
    posts.`date` DESC;
Comments