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!
All Content
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
www.cepfox.com
nice blog
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
www.firatoto.net
By sikiş at 12:41 p.m. on June 5, 2009
www.1kiz.com
By koxp at 7:40 p.m. on June 5, 2009
www.level83.blogspot.com
By koxp at 9:57 p.m. on June 5, 2009
www.level83.blogspot.com
By igvir at 12:08 a.m. on June 8, 2009
http://www.igvir.com
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
http://www.igvir.com
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
thank you
for dizi indir
http://www.gencdizi.com
for lost izle
http://lost6altincisezon.blogspot.com/
for books
http://www.mitosboyut.net
for anime
http://anime.gencdizi.com
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
http://www.fantastic-replica.net
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.