Login

Custom SQL via subquery

Author:
drg006
Posted:
May 14, 2007
Language:
Python
Version:
.96
Score:
9 (after 9 ratings)

You can use custom SQL statements with the existing database API by creating a subquery specified via the tables parameter of extra. (This has only been tested with MySQL and may not work with all database back-ends.)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
class ItemManager(models.Manager):
    def top_rated(self):
        subquery = """(SELECT app_rating.item_id, AVG(app_rating.value) AS value 
                       FROM app_rating 
                       GROUP BY app_rating.item_id) AS item_rating_avg"""
        condition = 'item_rating_avg.item_id = app_item.id' # Join
        order = '-item_rating_avg.value'
        return self.get_query_set().extra(tables=[subquery],
                                          where=[condition]).order_by(order)
    def most_discussed(self):
        subquery = """(SELECT app_comment.item_id, SUM(1) AS value 
                       FROM app_comment 
                       GROUP BY app_comment.item_id) AS item_comment_count"""
        condition = 'item_comment_count.item_id = app_item.id' # Join
        order = '-item_comment_count.value'
        return self.get_query_set().extra(tables=[subquery],
                                          where=[condition]).order_by(order)

class Item(models.Model):
    ...   
    objects = ItemManager()
    ...

class Comment(models.Model):
    item = models.ForeignKey(Item)
    text = models.TextField()
    user = models.ForeignKey(User)

class Rating(models.Model):
    item = models.ForeignKey(Item)
    value = models.IntegerField()
    user = models.ForeignKey(User)

More like this

  1. Template tag - list punctuation for a list of items by shapiromatron 11 months, 2 weeks ago
  2. JSONRequestMiddleware adds a .json() method to your HttpRequests by cdcarter 11 months, 3 weeks ago
  3. Serializer factory with Django Rest Framework by julio 1 year, 6 months ago
  4. Image compression before saving the new model / work with JPG, PNG by Schleidens 1 year, 7 months ago
  5. Help text hyperlinks by sa2812 1 year, 8 months ago

Comments

cahenan (on February 7, 2008):

Works with PostgreSQL too. Very useful, thanks.

#

Tobu (on August 15, 2008):

I'm using django trunk and unfortunately this doesn't work anymore, since django now cleanly escapes the table name.

#

Tobu (on August 15, 2008):

As far as GROUP BY is concerned, post queryset-refactor, I am able to get by with extra(select={'occurs': 'COUNT(*)'}), plus setting the undocumented qs.query.group_by.

#

Ceran (on July 30, 2011):

You can re-support subqueries by disabling the auto-quote feature with the following monkey-patch:

from django.db.models.sql.compiler import SQLCompiler
_quote_name_unless_alias = SQLCompiler.quote_name_unless_alias
SQLCompiler.quote_name_unless_alias = lambda self,name: name if name.startswith('(') else _quote_name_unless_alias(self,name)

#

Please login first before commenting.