CODEWITHSUNDEEP
phpmysqlsortingjoin
Richard
Richard

Reputation: 555

Sorting by article views high to low in last 10 minutes

I have a list of article links on a page that I get from mysql and I would like to sort them in a way that the most viewed articles in the past 10 minutes display first at the top of the other articles. Currently what I am trying is when someone clicks an article a new row is inserted into the table "views" with the columns id, timestamp and article_id. I have another table called articles which has all the article data and has the column article_id as well which matches any articles in the views table.

Basically, when an article is clicked it inserts rows into the views table like this.

id  timestamp               article_id
1   2018-03-24 16:02:16     12345
2   2018-03-24 16:02:18     54321
3   2018-03-24 16:02:20     12345
4   2018-03-24 16:02:22     12345
5   2018-03-24 16:02:24     23456
6   2018-03-24 16:02:26     23456
7   2018-03-24 16:02:28     54321
8   2018-03-24 16:02:30     23456
9   2018-03-24 16:02:32     12345
10  2018-03-24 16:02:34     34567
11  2018-03-24 16:02:36     34567
12  2018-03-24 16:02:38     54321
13  2018-03-24 16:02:40     12345
14  2018-03-24 16:02:42     12345
15  2018-03-24 16:02:44     12345

When results are displayed on the page i'd like to show the most clicked first as I said above. In this example 4 articles out of the 15 displayed have views/clicks so there is multiple rows of those 4 articles in the views table which need to be counted and displayed first from highest to lowest and then have the rest of the normal article results with no clicks show as usual. Something like this if it makes sense.

Results:
article_1 - article_id 12345 - 6 views
article_2 - article_id 54321 - 3 views
article_3 - article_id 23456 - 3 views
article_4 - article_id 34567 - 2 views
article_5 - article_id 11111 - 0 views
article_6 - article_id 22222 - 0 views
article_7 - article_id 33333 - 0 views
article_8 - article_id 44444 - 0 views
article_9 - article_id 55555 - 0 views
article_10 - article_id 66666 - 0 views
article_11 - article_id 77777 - 0 views
article_12 - article_id 88888 - 0 views
article_13 - article_id 99999 - 0 views
article_14 - article_id 111111 - 0 views
article_15 - article_id 222222 - 0 views

I get quite a lot of traffic on the site that I am trying to do this on so I am trying to figure out the best way to execute the query and also try not to take a significant performance hit if possible.

I am unsure how to query the articles table to get all the article info and then count and join the views table to get the view count for each article that had views within the last 10 minutes time period and sort them by the viewed articles first. I have been trying to come up with a query to do this but it never ends up working the way I have described.

Does anyone have any ideas or suggestions? Thanks.

EDIT:

Articles table:
id, article_id, title, description

Views table:
id, timestamp, article_id

Upvotes: 0

Views: 65

Answers (2)

Raymond Nijland
Raymond Nijland

Reputation: 11602

I am unsure how to query the articles table to get all the article info and then count and join the views table to get the view count for each article that had views within the last 10 minutes time period and sort them by the viewed articles first.

Your query needs to be something like these queries below. 

SELECT 
   Articles.id
 , COUNT(*) AS views
FROM 
 Articles 
LEFT JOIN 
 Views
ON
     Articles.id = Views.article_id 
   AND
     Views.timestamp >= NOW() - INTERVAL 10 MINUTE
GROUP BY
 Articles.id
ORDER BY
 COUNT(*) DESC

or 

SELECT 
   Articles.id
 , COUNT(*) AS views
FROM 
 Articles
LEFT JOIN 
 Views
ON
     Articles.id = Views.article_id 
   AND
     Views.timestamp >= NOW() - INTERVAL 10 MINUTE
GROUP BY
 Articles.id
ORDER BY
 CASE
  WHEN views >= 1
  THEN 1
  ELSE 2 
 END

Upvotes: 1

nitrex
nitrex

Reputation: 542

In your case, I would advise that you add another stats table, that counts views and link it to your articles table, whenever a user views the article, you increment the views value for this article_id.

also on inserting new article, it should add it in the stats table as 0 to get the no views result.

Upvotes: 0

Related Questions