如何获取所有谁是下一个帖子的用户

Question

我有跟踪每个相对于每个岗位的用户活动的一个表：

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) 
); 

我想获取所有当前谁是用户的ID在给定的职位之后。这将转化为获取所有最后一次“跟随”或“取消关注”操作为“跟随”的用户的ID。我想出来的是这样的：

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

然后我遍历所有的结果和存储这些的IDS，其最后一个动作是按照职位：

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

然而，这ISN没有工作。它存储最后一次操作取消关注帖子的用户的ID。我哪里错了？提前致谢。

UPDATE

我认为这个问题来自MySQL查询。其背后的想法是选择所有遵循或取消关注帖子的用户，然后按日期排序，并使用GROUP BY子句将选择缩小为每个用户执行的LAST操作。但是，该查询似乎是在进行订购前的分组。这是它应该发生的事情吗？我该如何解决它？

Answer 1

我想我明白了：

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; 

然后我做PHP的循环：

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

它仍然是不错的，直接拿到编号从查询，但这应该做。

Answer 2

使用MAX()为uts列，因为它包含时间戳。虽然您使用的是int类型，但推荐使用datetime。

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; 

Answer 3

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

子选择将获取每个用户后1（如果有的话）的最后一个动作，以及主查询，仅得到那些实际上follow行动。

Answer 4

有没有必要在PHP中处理它。你可以要求数据库管理系统为你做它：

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 

鉴于以下数据：

+------------+---------+---------+----------+ 
| 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 | 
+------------+---------+---------+----------+ 

这个查询将返回：

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