Hi Russ, Thanks for getting back to me. Ticket 11305 looks like what I need, but the code is extremely hacky. The second link you pasted seems to take the right approach, and I agree with you that the F() object should be used.
If I create a sane/clean patch (with F() support) as a stand alone class, would you consider having it merged into the core? Cal On Thu, Jun 16, 2011 at 12:27 PM, Russell Keith-Magee < russ...@keith-magee.com> wrote: > On Thu, Jun 16, 2011 at 7:20 PM, Cal Leeming [Simplicity Media Ltd] > <cal.leem...@simplicitymedialtd.co.uk> wrote: > > Hey guys, > > I've picked through as much as the Django docs as possible, but can't > seem > > to find any reference to Sum() allowing any in-method conditionals (i.e. > a > > conditional to Sum() with an in-line IF(), without the use of a > > WHERE/filter). > > The original query in MySQL is: > > > > mysql> select SUM(is_spam) as is_spam, SUM(is_image_blocked) as > > is_image_blocked, SUM(IF(dl_job_state = 2, 1, 0)) as dl_job_success > COUNT(*) > > as total_rows from fourchan_post; > > +---------+------------------+----------------+------------+ > > | is_spam | is_image_blocked | dl_job_success | total_rows | > > +---------+------------------+----------------+------------+ > > | 9116 | 266516 | 5010939 | 38832166 | > > +---------+------------------+----------------+------------+ > > 1 row in set (3 min 13.14 sec) > > > > In Django, I'm using (incomplete): > >>>> Post.objects.aggregate(Count('id'), Sum('is_spam'), > >>>> Sum('is_image_blocked'), Sum('is_checked')) > > So far, the only way I can see to do this, would be to do a filter() > before > > the aggregate, but this will affect the other Sum()'s which would mean > > multiple queries would be necessary. Normally this wouldn't be an issue, > but > > the table has over 40 million rows lol (and it already takes well over 3 > > minutes to execute) > > Ideally, I'd like to try and find a way (within the ORM), to specify an > IF > > conditional for the Sum(), thus only having to perform a single query. If > > this isn't possible, I'll put in a feature request for it. > > I haven't looked into your specific use case in detail, but it *might* > be possible to do this with a custom aggregate. SUM, COUNT etc are all > just classes in Django, so you can extend and define your own if you > want. > > Ticket #11305 [1] seems to describe almost exactly the feature you are > asking for (although they wanted COUNT, not SUM); in the discussion > for the ticket, Alex gives a code sample implementing conditional > aggregates. > > There were also discussions on django-users back when aggregates were > introduced, describing how to define a custom aggregate [2]. > > Both these references are worth a look. > > [1] https://code.djangoproject.com/ticket/11305 > [2] > http://groups.google.com/group/django-users/browse_thread/thread/bd5a6b329b009cfa > > Yours, > Russ Magee %-) > > -- > You received this message because you are subscribed to the Google Groups > "Django users" group. > To post to this group, send email to django-users@googlegroups.com. > To unsubscribe from this group, send email to > django-users+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/django-users?hl=en. > > -- You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com. To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/django-users?hl=en.