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.

Answer 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.

Answer 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.

Answer 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