0

I'm trying to obtain a list of articles and order them by their popularity over time. For example, older articles should rank lower even if they have a higher number of views.

In order to do this each article has a view count and a posted date. I'm guessing the simplest way would be to divide the article view count by the date posted... something like:

(view_count+comment_count) / date_posted = trend_score

I'm trying to understand if this is possible with the Django ORM, even if it is raw SQL? Would appreciate any help.

Hanpan
  • 10,013
  • 25
  • 77
  • 115

1 Answers1

1

I guess the simpliest and most effective way to do is to add a trend_score field to your model and update it when the model is saved (you neeed to save the model anyways if you have a view count/comment count on it). Then you can easily filter by this field. You can fore sure do it somehow with SQL, but if you have to update the values you need to update already within your model, calculate also the score upon saving.

Bernhard Vallant
  • 49,468
  • 20
  • 120
  • 148
  • But if the article is older and hasn't been viewed in a while, the trend score may not get updated with this method? – Hanpan May 24 '13 at 10:15
  • if the view count, comment count and date posted haven't changed the score stays the same? Or are you talking about migrating old articles to this system? Then you could just add the field and call `save()` on all articles (assuming the score calculation happens in the `save` method...)... – Bernhard Vallant May 24 '13 at 10:36
  • It's a good solution but we have thousands of articles so updating each one when posting a new article isn't ideal. I suppose I could set this up as a nightly cron or something like that and archive really old articles to keep things ticking over nicely. – Hanpan May 24 '13 at 11:19
  • I didn't mean to update all articles when posting a new one! I just meant that you have to do this _once_ if you introduce this new system! – Bernhard Vallant May 24 '13 at 12:42