Secrets of the Django ORM

You won't see this in the Django documentation, you won't see it mentioned on other blogs, and you certainly won't hear the core developers of Django boasting about it, but Django's ORM has a secret weapon: it supports SQL group_by and having clauses, and it has for quite some time.

It's not part of the public QuerySet API, but rather a part of the private Query API. But just because it's not part of the public API doesn't mean that it's not easy to use--it just means that it might change in the future. So it's really a "use at your own risk" type of deal now. If you're up for keeping on top of things so that you know what to change when the next version of Django comes out, then read on. First, let's start with some model definition:

class TumbleItem(models.Model):
    title = models.CharField(max_length=255)
    item_type = models.CharField(max_length=50)

    def __unicode__(self):
        return '%s: "%s"' % (self.item_type, self.title)

A simple tumblog item. Very simple, as in, not really useful at all. But that's OK since this is just a demonstration. To demonstrate, let's create some data:

>>> ti1 = TumbleItem.objects.create(title='Blog Post 1', item_type='blog')
>>> ti2 = TumbleItem.objects.create(title='Blog Post 2', item_type='blog')
>>> ti3 = TumbleItem.objects.create(title='Blog Post 3', item_type='blog')
>>> ti4 = TumbleItem.objects.create(title='Article Dugg 1', item_type='digg')
>>> ti5 = TumbleItem.objects.create(title='Article Dugg 2', item_type='digg')
>>> ti6 = TumbleItem.objects.create(title='Link Saved 1', item_type='link')

OK now that we've loaded some data, let's use the group_by functionality!

>>> qs = TumbleItem.objects.all()
>>> qs.query.group_by = ['item_type']
>>> item_types = [i.item_type for i in qs]
>>> item_types
[u'blog', u'digg', u'link']

There we go, it's quick, easy, and it seems to Just Work. But let's try to grab only the item_types which have more than one item:

>>> qs = TumbleItem.objects.all()
>>> qs.query.group_by = ['item_type']
>>> qs.query.having = ['COUNT(item_type) > 1']
>>> item_types = [i.item_type for i in qs]
>>> item_types
[u'blog', u'digg']

And now we've successfully used the group_by and having functionality in the Django ORM. I'm excited for some aggregation functionality to start being exposed as a public API, as I'm sure it will be more elegant than this solution, but at the same time this is a neat hidden secret in the Django ORM. Well now you have the knowledge, so you have the power, and it's up to you to use it wisely!

111 Comments So Far...

By SeanOC at 2:01 p.m. on Nov. 6, 2008

Awesome tip! This will help me get rid of a lot of nasty "extra" statements and custom SQL.

 

By Jökull at 2:41 p.m. on Nov. 6, 2008

A.w.e.s.o.m.e

 

By Martin at 3:08 p.m. on Nov. 6, 2008

Great! I get rid of hundred lines of raw-sql just for this aggregation stuff.

Is it possible to do a count(*) query for grouped lines in this way?

 

By Arthur at 3:26 p.m. on Nov. 6, 2008

Great tip, I had always thought these features will be added with the aggregation support in 1.1, and didn't know they were already there—Thanks!

 

By Benjamin Golub at 3:53 p.m. on Nov. 6, 2008

I had *no* clue this was possible. It would have helped a lot in building RSSmeme! Thanks Eric.

 

By barbara at 7:50 p.m. on Nov. 6, 2008

Oh nice - thanks for that. :)

 

By Nicolas Lara at 11:27 a.m. on Nov. 7, 2008

Hi,
You might want to check out the latest patch on ticket #3566 which might make it to trunk soon (I hope =) ). It is important especially because the these tricks will probably break with public API aggregation support.
Very good hidden tip for the time been though =)

 

By massimo at 2:53 p.m. on Nov. 7, 2008

Thanks for this post. It was inspirational. ;-)
http://groups.google.com/group/web2py/t/22cb295d35c2a7f8

 

By lianyk at 7:15 p.m. on Nov. 7, 2008

You might want to check out the latest patch on ticket #3566 which might make it to trunk soon (I hope =) ). It is important especially because the these tricks will probably break with public API aggregation support.

 

By wow gold at 7:19 p.m. on Nov. 7, 2008

Great! I get rid of hundred lines of raw-sql just for this aggregation stuff.

 

By wow gold at 7:20 p.m. on Nov. 7, 2008

I'm glad to hear it! Shoot me an e-mail if you have any problems.

 

By wow gold at 9:21 p.m. on Nov. 7, 2008

yeah
!

 

By cheapest wow gold at 9:21 p.m. on Nov. 7, 2008

yes!!!

 

By Andreas at 8:49 a.m. on Nov. 8, 2008

qs.query.group_by = ['item_type']

seems to have problems with postgresql.

 

By Andreas at 9:08 a.m. on Nov. 8, 2008

additional:

ProgrammingError: ERROR: column id must appear in the GROUP BY clause or be used in an aggregate function

 

By Malcolm Tredinnick at 8:48 p.m. on Nov. 8, 2008

You've pointed out that this is internal API and it's going to be one of those cases where it really will change. The "having" implementation is very fragile at the moment and has to be changed (it will end up looking more like Query.where). So any code treating it as a list won't work on trunk very shortly.

I hope people are really, really paying attention when they use internal stuff like this and have lots of tests so that they will notice when the internal code changes.

 

By omtv at 10:17 p.m. on Nov. 8, 2008

This is something i definitely will use in my code. Thanks!

 

By Nick at 3:14 a.m. on Nov. 9, 2008

Hmm, after reading a lot of enthusiastic comments I don't think that sharing of tips like that is very useful.

But anyway thanks for sharing!
I will not use it in my projects because I have a lot of code already written and tested.I will wait when it will become "regular" magic instead of "black".

 

By Paul @ Web design Ireland at 8:24 a.m. on Nov. 12, 2008

Neato!

Bookmarked.to.delicious.

 

By slavus at 11:49 a.m. on Nov. 12, 2008

This is great.

 

By isaac at 1:23 p.m. on Nov. 12, 2008

yes, this is a great find and we've been working on finding a solution for aggregate functions.

Thanks

 

By Emily Wall at 4:03 p.m. on Nov. 12, 2008

2mmy8bvajdecb7wl

 

By Nick at 7:32 p.m. on Nov. 17, 2008

Any ideas how to make resulting queryset respect order_by ?

 

By inwowgold at 1:41 a.m. on Dec. 1, 2008

B.w.e.s.o.m.e

 

By john at 3:01 a.m. on Dec. 4, 2008

good writen

 

By Koxp at 11:04 a.m. on March 13, 2009

Do you good

 

By vtunnel at 11:05 a.m. on March 13, 2009

Great article <a href="http://www.youtubekaydet.com" title="youtube">youtube</a> thank you very much

Great article <a href="http://www.vtunnel.com" title="vtunnel">vtunnel</a> thank you very much

 

By YOUTUBE at 11:06 a.m. on March 13, 2009

Great article <a href="http://www.youtubekaydet.com" title="youtube">youtube</a> thank you very much

Great article <a href="http://www.vtunnel.com" title="vtunnel">vtunnel</a> thank you very much

 

By Oyun Download at 8:02 a.m. on March 18, 2009

For the aricle has news

 

By Online Games at 8:04 a.m. on March 18, 2009

Activate there was char

 

By Laptop Notebook at 8:07 a.m. on March 18, 2009

For was where work

 

By Emin Ergin at 9:33 p.m. on March 18, 2009

Good has ready the

 

By Emin Girgin at 6:51 p.m. on March 19, 2009

Mail the problem your blog

 

By Barbi Oyunlari at 6:52 p.m. on March 19, 2009

For problem network updated

 

By wholesale jewelry at 8:59 p.m. on March 24, 2009

Good site,thank you!

 

By wholesale jewelry at 7:26 a.m. on April 7, 2009

seems to have problems with postgre sql

 

By forex megadroid at 5:06 a.m. on April 10, 2009

top50watches.com- online store for Swiss watches,our Swiss watches are of top quality and most authentic looking designer at cheap / discount price.

 

By live at 8:54 a.m. on April 17, 2009

Great info, thanks for sharing

 

By Free DSi at 6 a.m. on April 25, 2009

Top article mate! nice one!

 

By wholesale jewelry at 10:17 p.m. on May 3, 2009

Good site,it is very useful.thks/

 

By Baz Taylor at 9:06 p.m. on May 6, 2009

Great article. Thanks for the info.

 

By tibet tour at 4:33 a.m. on May 7, 2009

good,i most like

 

By cheap wow gold at 12:24 a.m. on May 9, 2009

For this matter, once I discussed with one of my friends, not only about the content you
talked about, but also to how to improve and develop, but no results. So I am deeply moved by
what you said today.

 

By wholesale jewelry at 4:24 a.m. on May 14, 2009

Good info.thks alot.

 

By lida diyet zayıflama r10seoogle at 6:09 a.m. on May 16, 2009

bitki caylari <a href="http://lidaal-zayiflama.klipci.org/" title="lida diyet zayıflama r10seoogle">lida diyet zayıflama r10seoogle</a> zayiflama haplari
<a href="http://klipci.org/" title="klip izle">klip izle</a> video izle mp3 dinle

 

By lida diyet zayıflama r10seoogle at 6:09 a.m. on May 16, 2009

Great info, thanks for sharing

 

By good at 8:02 a.m. on May 18, 2009

 

By 925 silver jewelry at 2:02 a.m. on May 20, 2009

Good info.thks alot

 

By ben10 oyunları at 3 a.m. on May 25, 2009

Mail the problem your blog

 

By 小遊戲 at 4:32 a.m. on May 26, 2009

Mail the problem your blog

 

By cheap chanel handbags at 11:51 a.m. on May 26, 2009

like it very much ,so good

 

By cheap nike shoes at 11:52 a.m. on May 26, 2009

also so good

 

By wow gold at 3:54 a.m. on May 27, 2009

thank you

 

By wow gold at 7:21 p.m. on May 27, 2009

yes, this is a great find and we've been working on finding a solution for aggregate functions.

 

By wholesale jewelry at 2:57 a.m. on May 29, 2009

thanks very much

 

By china travel agency at 10:20 a.m. on May 29, 2009

For this matter, once I discussed with one of my friends, not only about the content you
talked about, but also to how to improve and develop, but no results. So I am deeply moved by
what you said today.

 

By porno at 1:29 p.m. on May 29, 2009

For this matter, once I discussed with one of my friends, not only about the content you

 

By ed hardy at 8:09 a.m. on May 30, 2009

thanks very much

 

By Juergen Brendel at 9:03 p.m. on May 31, 2009

Someone asked about COUNT() in conjunction with 'group_by'. I had the same problem and arrived at a solution, which I detailed here: http://www.brendel.com/consulting/blog/2009/06/how-to-do-count-with-group-by-in-django.html

 

By club penguin at 9:22 p.m. on May 31, 2009

You've pointed out that this is internal API and it's going to be one of those cases where it really will change. The "having" implementation is very fragile at the moment and has to be changed.

 

By ekin turkmen at 2:09 a.m. on June 1, 2009

sagol

 

By laptop batteries at 3:38 a.m. on June 2, 2009

The "having" implementation is very fragile at the moment and has to be changed.

 

By FPCBP115 at 3:41 a.m. on June 2, 2009

Someone asked about COUNT() in conjunction with 'group_by'. I had the same problem and arrived at a solution

 

By replica watches at 3:42 a.m. on June 2, 2009

I had the same problem and arrived at a solution

 

By aaa at 8:22 a.m. on June 4, 2009

nice blogger

 

By aaaa at 8:23 a.m. on June 4, 2009

 

By sikiş at 12:41 p.m. on June 5, 2009

 

By koxp at 7:40 p.m. on June 5, 2009

 

By koxp at 9:57 p.m. on June 5, 2009

 

By igvir at 12:08 a.m. on June 8, 2009

 

By alex at 4:05 a.m. on June 10, 2009

but Django's ORM has a secret weapon: it supports SQL group_by and having clauses, and it has for quite some time.

 

By igvir at 4:26 a.m. on June 12, 2009

 

By Sikiş at 6:53 p.m. on June 12, 2009

Good a black eric

 

By wow gold at 3:48 a.m. on June 13, 2009

welldone.

 

By Cheap WoW Gold at 4:11 a.m. on June 13, 2009

Thank you for the specific info you providing in the post.

 

By Flash Game at 8:12 p.m. on June 13, 2009

great thank you

 

By injection molding at 9:48 p.m. on June 13, 2009

nice site for us!

 

By louis vuitton at 12:08 a.m. on June 16, 2009

when i use the code but wrong....
help me

 

By replica louis vuitton at 1:33 a.m. on June 16, 2009

My answer is the same to you.I'm excited for some aggregation functionality to start being exposed as a public API,talked about, but also to how to improve and develop

 

By 312-0292 at 2:43 a.m. on June 16, 2009

Thank you for the specific info you providing in the post.

 

By 381373-001 at 2:43 a.m. on June 16, 2009

but also to how to improve and develop

 

By PA3154U-1BAS at 2:44 a.m. on June 16, 2009

My answer is the same to you.I'm excited for some aggregation functionality to start being exposed as a public API,talked about

 

By laptop batteries at 2:45 a.m. on June 16, 2009

You won't see this in the Django documentation, you won't see it mentioned on other blogs, and you certainly won't hear the core developers of Django boasting about it, but Django's ORM has a secret weapon: it supports SQL group_by and having clauses, and it has for quite some time.

 

By replica watches at 2:45 a.m. on June 16, 2009

SQL group_by and having clauses, and it has for quite some time.

 

By Metin2 at 7:10 a.m. on June 17, 2009

thank you admin

 

By Video izle at 7:11 a.m. on June 17, 2009

thank you very much =)

 

By Dizi izle at 7:11 a.m. on June 17, 2009

very verry much

 

By NesMedya Haber Video Oyun Seo Yarışması at 8 p.m. on June 17, 2009

Thank you for the specific info you providing in the post.

 

By Jordan Shoes at 10:19 p.m. on June 19, 2009

awewsome! great job!

 

By Lida diyet zayıflama r10seoogle at 5:33 a.m. on June 20, 2009

SQL group_by and having clauses, and it has for quite some time.

 

By cheap louis vuitton at 8:47 p.m. on June 20, 2009

thank you ! perfect

 

By koxp at 5:11 a.m. on June 21, 2009

Thanxxx four articles

Me ?
<a href="http://www.koxpturkey.com">Koxp</a>
<a href="http://www.koxpturkey.com">Knight Online</a>
<a href="http://www.koxpturkey.com">Karahan Online</a>

 

By Silkroad at 3:26 p.m. on June 22, 2009

ogrencifan

 

By lingerie at 8:03 p.m. on June 22, 2009

In regards to composition and django, the one thing I would like to see supported someday, (but I don't have the chops to even attempt to add this to django itself), is the concept of partials

 

By lost at 3:21 p.m. on June 23, 2009

 

By sXe at 4:50 a.m. on June 24, 2009

THank You ..

 

By Van at 8:55 a.m. on June 24, 2009

Rather than having to change after the next version of Django it would be nice if its implemented into the update.

 

By jordan shoes at 1:41 a.m. on June 25, 2009

thank you ! perfect

 

By jordan shoes at 1:42 a.m. on June 25, 2009

SQL group_by and having clauses, and it has for quite some time.

 

By ugg boots at 1:42 a.m. on June 25, 2009

awewsome! great job!

 

By tiffany jewellery at 1:43 a.m. on June 25, 2009

Rather than having to change after the next version of Django it would be nice if its implemented into the update.

 

By lida at 8:55 a.m. on June 25, 2009

lida resmi satis sitesi nice blog

 

By diyet at 6:19 p.m. on June 25, 2009

thanks for this great post. You can check out <a href="http://www.dogal-zayiflama.com" title="Zayıflama">zayıflama</a> and nice blog

 

By zayıflama at 6:26 a.m. on June 26, 2009

thanks for this post admin.

 

By çini at 6:57 a.m. on June 26, 2009

great post thank you

 

By dj at 8:40 a.m. on June 26, 2009

Thank you so much for content, you would track;)

 

By sare at 4:26 a.m. on June 30, 2009

 

By Stop Dreaming Start Action at 7:08 a.m. on July 2, 2009

Thanks for sharing your thought. Wish you good

 

By Rusli Zainal Sang Visioner at 7:08 a.m. on July 2, 2009

i feel lucky can find this usefull informations..
thanks for this great posting..

 

By Jumoke at 2:16 p.m. on July 2, 2009

Hi, I have been here for several times now and thought to sign your guestbook. Very nice! Keep up the good work.
I am from Leone and too bad know English, give true I wrote the following sentence: "Definition of flea from the merriam webster online dictionary with audio pronunciations, thesaurus, word of the day, and word games."

Best regards :), Jumoke.

 

By MMO at 2:35 a.m. on July 3, 2009

Django ORM is interesting lol.

 

Voice your opinion...