Django 1.2 is more than happy to filter on aggregates and annotations, see: http://docs.djangoproject.com/en/1.2/topics/db/aggregation/#aggregations-and-other-queryset-clauses
My problem is that I can't see how to specify this additional computed field as an annotation, and can only coerce it in with extra(), which doesn't allow filtering, and no way to manually insert a HAVING clause. With this particular data set, I found a simpler way to exclude the tuples I wasn't interested in (filtering on the field that my annotation is based upon), but I would still be interested if there is a way to do this. Cheers Tom On Fri, May 28, 2010 at 11:29 AM, Euan Goddard <[email protected]> wrote: > Hi Tom, > > Whilst Django annotation is great, it is rather simplistic in nature > and can only do very basic Count, Sum, Avg, etc. which do not allow > any filtering (i.e. a WHERE clause in the SQL). > > Unless there are major differences in 1.2 (which I haven't seen) then > you won't be able to do what you want I'm afraid. > > Euan > > On May 28, 10:26 am, Tom Evans <[email protected]> wrote: >> Hi all >> >> I want to annotate a qs with a computed field from one of its current >> fields, and then filter on the annotated field. I cannot work out how >> I'm supposed to do this in the ORM (or even if I can). >> >> Basically, I have a model with a username field, that in this case >> contains an email address. I want to extract the domain of that email >> address, and then filter the queryset based upon the value of the >> domain. >> >> So, in code: >> >> domains = UsageLogEntry.objects \ >> .filter(action=ACTION_FAILED_REGISTRATION, >> sub_action=SUB_ACTION_DOMAIN_DISALLOWED) \ >> .annotate(domain=...) \ >> .exclude(domain='').values_list('domain', flat=True) >> >> Im clueless about what to put in annotate(). >> >> In 1.0.x, I used a horrific hack with extra(), that has stopped >> working with 1.2: >> >> domains = UsageLogEntry.objects \ >> .filter(action=ACTION_FAILED_REGISTRATION, >> sub_action=SUB_ACTION_DOMAIN_DISALLOWED) \ >> .extra( >> select={ 'domain': 'SUBSTRING(username, LOCATE("@", username)+1)' >> }, >> # XXX hacks ahoy - this makes the where clause look like >> # WHERE ... AND 1 HAVING `domain` != '' >> where=[ "1 HAVING `domain` != ''", ], >> ) \ >> .order_by('domain') \ >> .values_list('domain', flat=True) >> >> This is because (I think) in 1.0.x, the where clause was bare, and in >> 1.2 it is enclosed by braces, which makes my hacky insertion of a >> having clause illegal syntax. >> >> Thanks for any pointers >> >> Tom > > -- > You received this message because you are subscribed to the Google Groups > "Django users" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > 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 [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/django-users?hl=en.
