MySQL: Cascade filtering by date intervals

Question

Can any help me with such type of query.

I have:

posts table
comments table

They are linked through comments.post_id = posts.post_id columns.

I user can filter post by comments for the past:

1 hour
24 hours
2 days, etc.

If user selected to show posts for the past 1 hour, but there no posts this period, we need to go step by step:

Select posts for past 1 hour, if empty - for past 24 hours, if empty - for past 2 days, if empty - since inception (without any conditions).

Could anyone please help me to build such query?

UPD

"Filter posts by comments" means sort by comments count.

So actually goal is request "Show me posts sorted by comments count that have been left for the past XXX hours".

And if is selected "for the past hour" but there are no posts with comments left for the past 1 hour, we need to fetch posts with comments left for the past 24 hours (sorted by comments count) and so on.

Tables structure

Posts:

post_id

title

content

date_added

Comments

comment_id

content

post_id

date_added

So link is posts.post_id = comments.post_id.

I would like to have next result when user view most commented posts for the past hour:

 posts.post_id | comments_count | posts.date_added | group
---------------+----------------+------------------+----------------
           156 |              8 |       2013-04-02 | hour
           154 |              3 |       2013-04-02 | hour
           129 |              1 |       2013-03-10 | 24 hours
           13  |             14 |       2013-02-18 | 48 hours
           138 |              6 |       2013-03-29 | week
           137 |              4 |       2013-03-29 | week
           161 |             21 |       2013-04-11 | month
           6   |              2 |       2013-01-24 | year
           103 |              8 |       2013-03-02 | since inception

Results sorted by:

Top of the list is 2 posts that have been commented due the past hour, and ordered by comments count.
Next we place posts that have been commented due the past day.
Next — posts commented due past 2 days
posts commented due past week, and again they should be ordered by comments count
For the past month
For the past year
In the end of this list we need to place articles that have been commented more than year ago, and they also should be ordered by comments count.

Thanks in advance.

I think your best solution is to wrap each query in `if()` statements. If the record count is 0 then run the next query. — Matt Busche, Apr 01 '13 at 15:49
but that filter intervals are stored in another table, so admin may increase number of intervals. E.g. he can create another filter "for the past week". So in this case we need to modify query each time when filters changed — balkon_smoke, Apr 01 '13 at 15:53
if he can create a new filter then just loop over a query that contains all the possible date ranges — Matt Busche, Apr 01 '13 at 15:54
@balkon_smoke Can you post some sample data for each table and then the desired result with the filtering? — Taryn, Apr 11 '13 at 11:30
@bluefeet I updated the question, please check it out, I will be very grateful for any help — balkon_smoke, Apr 11 '13 at 13:07
@balkon_smoke Can you post the structure of `intervals` table...... — Meherzad, Apr 12 '13 at 17:13

score 1 · Accepted Answer · answered Apr 01 '13 at 15:57

Calculate the most recent comment for each group. Then use this to choose which group you want. You can do this calculation with a subquery:

select p.* c.*
from posts p join
     comments c
     on p.post_id = posts.post_id join
     (select post_id, max(postdate) as postdate
      from comments
      group by post_id
     ) cmax
     on cmax.post_id = p.post_id
where (case when timestampdiff(minute, now(), cmax.timestamp) <= 60
            then timestampdiff(minute, now(), c.timestamp) <= 60
            when  timestampdiff(minute, now(), cmax.timestamp) <= 60*24
            then timestampdiff(minute, now(), c.timestamp) <= 60*24
            . . .
      )

The syntax for the time comparison depends on whether the values are stored as timestamps or datetimes.

score 0 · Answer 2 · answered Apr 15 '13 at 09:12

If you want the top 5 posts in the last hour, assuming your date_added fields are timestamps, you can use:

SELECT   post_id,
         count(comment_id) as comments_count,
         posts.date_added, 'hour' as grouptime
FROM     posts
INNER JOIN comments
ON       posts.post_id = comments.post_id
WHERE    TIMESTAMPDIFF(HOUR, comments.date_added, NOW()) = 0
GROUP BY posts.post_id
ORDER BY count(comment_id) DESC
LIMIT 5

If you want all of them, just remove LIMIT. For the last 24 hours:

SELECT   post_id,
         count(comment_id) as comments_count,
         posts.date_added, '24 hours' as grouptime
FROM     posts
INNER JOIN comments
ON       posts.post_id = comments.post_id
WHERE    TIMESTAMPDIFF(HOUR, comments.date_added, NOW()) < 24
GROUP BY posts.post_id
ORDER BY count(comment_id) DESC
LIMIT 5

and so on for different time periods.

If you want to get all of them in one go, use UNION between these queries.

MySQL: Cascade filtering by date intervals

2 Answers2