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.
