CODEWITHSUNDEEP
phpmysql
Sophivorus
Sophivorus

Reputation: 3083

How to fetch all the users who are following a post

I have a table that keeps track of each users activity relative to each post:

CREATE TABLE users2posts (
uts int unsigned not null comment 'uts for Unix Time Stamp',
user_id int unsigned not null,
post_id int unsigned not null,
action set('follow','unfollow','upvote','downvote'),
PRIMARY KEY (uts,user_id,post_id)
);

I want to fetch the ids of all the users who are currently following a given post. This translates into fetching the ids of all the users whose last 'follow' or 'unfollow' action was 'follow'. What I've come up with is this:

SELECT user_id, action 
FROM users2posts 
WHERE post_id=1 
AND (action="follow" OR action="unfollow") 
GROUP BY user_id 
ORDER BY uts DESC;

Then I loop through all the results and store the ids of those whose last action was to follow the post:

foreach ($ROWS as $ROW) 
    if ($ROW['action'] == 'follow') 
        $FOLLOWERS[] = $ROW['user_id'];

However, this isn't working. It stores the ids of users whose last action was to unfollow the post. Where am I going wrong? Thanks in advance.

UPDATE

I think that the problem comes from the MySQL query. The idea behind it is to select all the users who followed or unfollowed the post, then order them by date, and shrink the selection to the LAST action each user performed, with the GROUP BY clause. However, the query seems to be grouping BEFORE ordering. Is that what it is supposed to happen? And how do I fix it?

Upvotes: 0

Views: 158

Answers (4)

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

There is no need to process it in PHP. You can ask the DBMS to do it for you:

select up1.user_id from users2posts up1
left join (
  select up3.user_id, up3.post_id, up3.uts from users2posts up3
  where up3.action in ('follow', 'unfollow')
) as up2
on up1.user_id = up2.user_id and up1.post_id = up2.post_id and up1.uts < up2.uts
where up1.action = 'follow' and up2.user_id is null and up1.post_id = 1

Given the following data:

+------------+---------+---------+----------+
|    UTS     | USER_ID | POST_ID |  ACTION  |
+------------+---------+---------+----------+
| 2000-01-01 |       1 |       1 | follow   |
| 2001-01-01 |       1 |       1 | unfollow |
| 2002-01-01 |       1 |       1 | follow   |
| 2000-01-01 |       2 |       1 | follow   |
| 2001-01-01 |       2 |       1 | unfollow |
| 2003-01-01 |       2 |       2 | follow   |
| 2004-01-01 |       3 |       1 | follow   |
| 2004-01-01 |       1 |       1 | upvote   |
| 2004-01-01 |       2 |       1 | downvote |
+------------+---------+---------+----------+

This query will return:

+---------+
| USER_ID |
+---------+
|       1 |
|       3 |
+---------+

Upvotes: 1

Sophivorus
Sophivorus

Reputation: 3083

I think I got it:

SELECT * FROM (
    SELECT * FROM users2posts 
    WHERE post_id=1 
    AND (action="follow" OR action="unfollow") 
    ORDER BY uts DESC
    ) AS temp 
GROUP BY user_id;

And then I do the loop with PHP:

foreach ($ROWS as $ROW) 
    if ($ROW['action'] == 'follow') 
        $FOLLOWERS[] = $ROW['user_id'];

It would still be nice to get the ids directly out of the query, but this should do.

Upvotes: 0

Shiplu Mokaddim
Shiplu Mokaddim

Reputation: 57650

Use MAX() for uts column as it contains the time stamp. Though you are using int type, datetime is recommended.

SELECT 
    MAX(uts) `last_action_time`,  -- max time is the most recent time
    user_id , 
    action 
FROM
    users2posts 
WHERE
    action='follow'  -- only track the follow action
GROUP BY 
    user_id, 
    action    -- if you omit action from here last action will be calculated  
              -- from both follow and unfolllow
ORDER BY
    uts 
        DESC;

Upvotes: 1

Christian Specht
Christian Specht

Reputation: 36421

select *
from users2posts
where uts in 
(
    select max(uts) as lastaction
    from users2posts 
    where post_id=1 
    group by user_id
)
and action = 'follow'

The sub-select will fetch the last action of each user for post 1 (if any), and the main query gets only those actions which were actually follow.

Upvotes: 1

Related Questions