Sorting products after dateinterval and weight

Question

What I want is to be able to get this weeks/this months/this years etc. hotest products. So I have a model named ProductStatistics that will log each hit and each purchase on a day-to-day basis. This is the models I have got to work with:

class Product(models.Model):
 name   = models.CharField(_("Name"), max_length=200)
 slug   = models.SlugField()
 description  = models.TextField(_("Description"))

 picture   = models.ImageField(upload_to=product_upload_path, blank=True)

 category  = models.ForeignKey(ProductCategory)

 prices   = models.ManyToManyField(Store, through='Pricing')

 objects   = ProductManager()

 class Meta:
  ordering = ('name', )

 def __unicode__(self):
  return self.name

class ProductStatistic(models.Model):
 # There is only 1 `date` each day. `date` is
 # set by datetime.today().date()
 date   = models.DateTimeField(default=datetime.now)
 hits   = models.PositiveIntegerField(default=0)
 purchases  = models.PositiveIntegerField(default=0)

 product   = models.ForeignKey(Product)

 class Meta:
  ordering = ('product', 'date', 'purchases', 'hits', )

 def __unicode__(self):
  return u'%s: %s - %s hits, %s purchases' % (self.product.name, str(self.date).split(' ')[0], self.hits, self.purchases)

How would you go about sorting the Products after say (hits+(purchases*2)) the latest week?

This structure isn't set in stone either, so if you would structure the models in any other way, please tell!

Answer 1

first idea:

in the view you could query for today's ProductStatistic, than loop over the the queryset and add a variable ranking to every object and add that object to a list. Then just sort after ranking and pass the list to ur template.

second idea:

create a filed ranking (hidden for admin) and write the solution of ur formula each time the object is saved to the database by using a pre_save-signal. Now you can do ProductStatistic.objects.filter(date=today()).order_by('ranking')

Both ideas have pros&cons, but I like second idea more

edit as response to the comment

• Use Idea 2

• Write a view, where you filter like this: ProductStatistic.objects.filter(product= aProductObject, date__gte=startdate, date__lte=enddate)

  loop over the queryset and do somthing like aProductObject.ranking+= qs_obj.ranking

• pass a sorted list of the queryset to the template

Basically a combination of both ideas

edit to your own answer

Your solution isn't far away from what I suggested — but in sql-space.

But another solution:

Make a Hit-Model:

class Hit(models.Model):
    date = models.DateTimeFiles(auto_now=True)
    product = models.ForeignKey(Product)
    purchased= models.BooleanField(default=False)
    session = models.CharField(max_length=40)

in your view for displaying a product you check, if there is a Hit-object with the session, and object. if not, you save it

Hit(product=product,
    date=datetime.datetime.now(),
    session=request.session.session_key).save()

in your purchase view you get the Hit-object and set purchased=True

Now in your templates/DB-Tools you can do real statistics.

Of course it can generate a lot of DB-Objects over the time, so you should think about a good deletion-strategy (like sum the data after 3 month into another model MonthlyHitArchive)

If you think, that displaying this statistics would generate to much DB-Traffic, you should consider using some caching.

Answer 2

I solved this the way I didn't want to solve it. I added week_rank, month_rank and overall_rank to Product and then I just added the following to my ProductStatistic model.

def calculate_rank(self, days_ago=7, overall=False):
 if overall:
  return self._default_manager.all().extra(
   select = {'rank': 'SUM(hits + (clicks * 2))'}
  ).values()[0]['rank']
 else:
  return self._default_manager.filter(
   date__gte = datetime.today()-timedelta(days_ago),
   date__lte = datetime.today()
  ).extra(
   select = {'rank': 'SUM(hits + (clicks * 2))'}
  ).values()[0]['rank']

def save(self, *args, **kwargs):
 super(ProductStatistic, self).save(*args, **kwargs)
 t = Product.objects.get(pk=self.product.id)
 t.week_rank = self.calculate_rank()
 t.month_rank = self.calculate_rank(30)
 t.overall_rank = self.calculate_rank(overall=True)
 t.save()

I'll leave it unsolved if there is a better solution.

Answer 3

Solved here: http://stackoverflow.com/questions/1603340/track-the-number-of-page-views-or-hits-of-an-object