Custom SQL via subquery

 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. Load customized SQL by roppert 5 years, 1 month ago
  2. custom sql without table names by robharvey 6 years, 11 months ago
  3. Custom SQL Function; Outputs Template-Friendly Content by hotani 6 years, 12 months ago
  4. Database Schema Upgrade (deprecated) by guettli 5 years, 5 months ago
  5. Export Database and Media_Root via Admin Interface by arne 6 years, 2 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)

#

(Forgotten your password?)