CODEWITHSUNDEEP
mysql
iBrazilian2
iBrazilian2

Reputation: 2293

SELECT most viewed for the last 24 hours

I want to retrieve the most viewed pages for a 24 hour period, yet I have not created a 1 to 1 relationship between the pages(posts) and the view count.

articles table

  id |   title   | content |   created  | view_count
-----+-----------+---------+------------+------------
  1  |  title-1  |  info-1 | 1386478352 |   2
  2  |  title-2  |  info-2 | 1386532855 |   90
  3  |  title-3  |  info-3 | 1386637325 |   25
  4  |  title-4  |  info-4 | 1386812249 |   5
  5  |  title-5  |  info-5 | 1387094028 |   10

I'm using the following to retrieve the most viewed of all time, but I need to reset this every 24 hours.

SELECT * FROM articles ORDER BY view_count DESC

Is there a way I can use information_schema for this instead of creating a 1 to 1 relationship between the articles and the view count and storing each time the article is viewed with the time the page was requests?

Upvotes: 1

Views: 322

Answers (2)

Markus Malkusch
Markus Malkusch

Reputation: 7878

I recommend not using any implementation detail of the underlying DBS. You will have hard times with migration/updates.

That said I suggest a simple 1:n relation Pageview(articleId, viewdate). Each user view adds a timestamp into Pageview. Then you can easily join, aggregate and do whatever you want with that information.

SELECT articles.*, COUNT(*) views24h
    FROM articles
    LEFT JOIN pageview ON pageview.articleId = articles.Id
               AND pageview.viewdate > SUBDATE(NOW(),1)
    GROUP BY article.id
    ORDER BY views24h DESC
    LIMIT 10;

Upvotes: 3

idmean
idmean

Reputation: 14895

You should change your database model.

For example create a table named with the columns date and article_id.

Then insert for every view a new row. Like:

INSERT INTO views (date, article_id) VALUES (UNIX_TIMESTAMP(),1)

Now you can make queries like this which return the views within the last 24 hours:

SELECT count(*) FROM views WHERE article_id = 1 AND date > UNIX_TIMESTAMP() - 86400

This would return all visits of the article with 1 one in the last 24h.

Upvotes: 1

Related Questions