Re: Annotating a queryset without aggregation

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 <tevans...@googlemail.com> 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 django-users@googlegroups.com.
To unsubscribe from this group, send email to django-users+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-users?hl=en.