MySQL：24 小时间隔连接

发布于 2024-11-27 15:52:30 字数 750 浏览 2 评论 0原文

我有以下查询：

select * from winners 
join profiles on winners.subscriber_id = profiles.subscriber_id 
join comments on comments.profile_id = profiles.vanity
join videos on winners.subscriber_id = videos.subscriber_id
join photos on winners.subscriber_id = photos.subscriber_id
where winners.round_id >= 4 AND winners.active = true 
AND (comments.created_at > DATE_SUB( NOW(), INTERVAL 24 HOUR) OR videos.created_at > DATE_SUB( NOW(), INTERVAL 24 HOUR) OR photos.created_at > DATE_SUB( NOW(), INTERVAL 24 HOUR)) AND comments.parent_id = 0;

此查询在 cron 作业上运行，该作业将提取每位获奖者过去 24 小时的最新信息，然后通过电子邮件向每位获奖者粉丝发送最新信息。

我遇到的问题是，查询会撤回误报，因为如果在过去 24 小时内发生对视频的评论，则会撤回视频和评论。 是否有限制获取过去 24 小时内发生的所有事情？

原文

I have the following query:

select * from winners 
join profiles on winners.subscriber_id = profiles.subscriber_id 
join comments on comments.profile_id = profiles.vanity
join videos on winners.subscriber_id = videos.subscriber_id
join photos on winners.subscriber_id = photos.subscriber_id
where winners.round_id >= 4 AND winners.active = true 
AND (comments.created_at > DATE_SUB( NOW(), INTERVAL 24 HOUR) OR videos.created_at > DATE_SUB( NOW(), INTERVAL 24 HOUR) OR photos.created_at > DATE_SUB( NOW(), INTERVAL 24 HOUR)) AND comments.parent_id = 0;

This query runs on a cron job that will pull the latest information for the past 24 hours for each winner and then will email each of the winners fans with the latest info.

The issues I am running into is that the query is pulling back false positives in that if a comment on a video happened in the past 24 hours then it pulls back the video and the comment. Is there anyway to limit to get EVERYTHING that has happened in the past 24 hours?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

贩梦商人 2024-12-04 15:52:30

我会将 24 小时检查移至连接。这样我们只加入最近更新的项目。

select * from winners 
    join profiles on winners.subscriber_id = profiles.subscriber_id
    left join comments on (comments.profile_id = profiles.vanity AND comments.created_at > DATE_SUB( NOW(), INTERVAL 24 HOUR))
    left join videos on (winners.subscriber_id = videos.subscriber_id AND videos.created_at > DATE_SUB( NOW(), INTERVAL 24 HOUR))
    left join photos on (winners.subscriber_id = photos.subscriber_id AND photos.created_at > DATE_SUB( NOW(), INTERVAL 24 HOUR))
where winners.round_id >= 4 AND winners.active = true 
AND comments.parent_id = 0;

I would move the 24 hour check to the join. That way we only join on items which have been updated recently.

select * from winners 
    join profiles on winners.subscriber_id = profiles.subscriber_id
    left join comments on (comments.profile_id = profiles.vanity AND comments.created_at > DATE_SUB( NOW(), INTERVAL 24 HOUR))
    left join videos on (winners.subscriber_id = videos.subscriber_id AND videos.created_at > DATE_SUB( NOW(), INTERVAL 24 HOUR))
    left join photos on (winners.subscriber_id = photos.subscriber_id AND photos.created_at > DATE_SUB( NOW(), INTERVAL 24 HOUR))
where winners.round_id >= 4 AND winners.active = true 
AND comments.parent_id = 0;

回复收藏 0 原文

~没有更多了~