Tutorial :Django 1.0/1.1 rewrite of self join



Question:

Is there a way to rewrite this query using the Django QuerySet object:

SELECT b.created_on, SUM(a.vote)  FROM votes a JOIN votes b ON a.created_on <= b.created_on  WHERE a.object_id = 1  GROUP BY 1  

Where votes is a table, object_id is an int that occurs multiple times (foreign key - although that doesn't matter here), and created_on which is a datetime.

FWIW, this query allows one to get a score at any time in the past by summing up all previous votes on that object_id.


Solution:1

I'm pretty sure that query cannot be created with the Django ORM. The new Django aggregation code is pretty flexible, but I don't think it can do exactly what you want.

Are you sure that query works? You seem to be missing a check that b.object_id is 1.

This code should work, but it's more than one line and not that efficient.

from django.db.models import Sum    v_list = votes.objects.filter(object__id=1)    for v in v_list:      v.previous_score = votes.objects.filter(object__id=1, created_on__lte=v.created_on).aggregate(Sum('vote'))["vote__sum"]  

Aggregation is only available in trunk, so you might need to update your django install before you can do this.


Solution:2

Aggregation isn't the issue; the problem here is that Django's ORM simply doesn't do joins on anything that isn't a ForeignKey, AFAIK.


Solution:3

This is what I'm using now. Ironically, the sql is broken but this is the gist of it:

    def get_score_over_time(self, obj):      """      Get a dictionary containing the score and number of votes       at all times historically      """      import pdb; pdb.set_trace();      ctype = ContentType.objects.get_for_model(obj)      try:          query = """SELECT b.created_on, SUM(a.vote)                          FROM %s a JOIN %s b                           ON a.created_on <= b.created_on                          WHERE a.object_id = %s                          AND a.content_type_id = %s                          GROUP BY 1""" % (              connection.ops.quote_name(self.model._meta.db_table),              connection.ops.quote_name(self.model._meta.db_table),              1,              ctype.id,              )          cursor = connection.cursor()          cursor.execute(query)          result_list = []          for row in cursor.fetchall():              result_list.append(row)      except models.ObjectDoesNotExist:          result_list = None      return result_list  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »