Re: Aggregate using extra() fields

[Kathleen Benitez]

Hm, I thought it was kind of self-evident.  Okay, here's the sample
Model definition:

class Records (models.Model):
   id = models.IntegerField(primary_key=True)
   state = models.TextField()
   race = models.TextField()
   age = models.IntegerField()
   sex = models.TextField()
   population = models.IntegerField()

Imagine it has 5 records
1  WA  Black  55   F  123
2  WA  White  55   F  556
3  WA  Asian  55   F  612
4  WI  Amer. Indian  55   F 125
5  WA  Unknown  55   F 23

So I know the population for a combination of demographics (from
census data, fwiw, though the example above is totally fabricated).
But I want to combine some groups and see what the new population
distribution is.  In this case, I want to create a generalized race
grouping, and I want to end up with something like this
Black  55  F  123
White  55  F  556
Other  55  F  760

My current approach is to add an extra field with the generalized race
classification
r = r.extra(select={'generalized_race: "CASE race WHEN 'Black' THEN
'B' WHEN 'White' THEN 'W' ELSE 'O' END"})

As mrts said, this does create a new field, but the field is
unavailable to use in further queries.  Below is what I would *like*
to workAdd the new field, then use the values() function to group
by all applicable fields.
fields = ['age', 'sex', 'generalized_race']
r = r.values(*fields).annotate(Pop = Sum('population'))

Any suggestions for alternate approaches?

~Kathy



On Sep 17, 3:29 am, mrts  wrote:
> On Sep 16, 11:30 pm, Kathleen Benitez 
> wrote:
>
> > I am attempting to evaluate some generalization schemes using Django.
> > In particular, I'd like to create a new field (generalized race), and
> > then find a total population using the values() function as follows:
>
> annotate() is somewhat confusing in your example, seeing the Record
> model and explicitly stating what you want to achieve with it
> would help us to help you better.
>
> > 1  r = Records.objects
> > 2  r = r.extra(select={'generalized_race: "CASE race WHEN 'Black' THEN
> > 'B' WHEN 'White' THEN 'W' ELSE 'O' END"})
>
> This is indeed "registered with Django":
>
> >>> rs = Record.objects.extra(select={'generalized_race':
>
> ... "CASE race WHEN 'Black' THEN'B' WHEN 'White' THEN 'W' ELSE 'O'
> END"})>>> rs[0].generalized_race
>
> u'B'
>
> However, it is correct that although extra(select={'foo': ...})
> augments the results with the field foo, it is not a proper
> models.Field and can not be referenced in further QuerySet methods.
>
> > 3  fields = ['age', 'sex', 'generalized_race']
> > 4  r = r.values(*fields).annotate(Pop = Sum('population'))
>
> Are you sure you need the values() call here? If you really don't
> want to select other fields, only() works equally well and retains
> the generalized_race pseudo-field:
>
> >>> fields = ['age', 'sex']
> >>> rs.only(*fields)[0].generalized_race
>
> u'B'
>
> As for the annotate() clause, it is unclear where the population comes
> from and what's the intent. Please clarify.
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---

Re: Aggregate using extra() fields

[mrts]

On Sep 16, 11:30 pm, Kathleen Benitez 
wrote:
> I am attempting to evaluate some generalization schemes using Django.
> In particular, I'd like to create a new field (generalized race), and
> then find a total population using the values() function as follows:

annotate() is somewhat confusing in your example, seeing the Record
model and explicitly stating what you want to achieve with it
would help us to help you better.

> 1  r = Records.objects
> 2  r = r.extra(select={'generalized_race: "CASE race WHEN 'Black' THEN
> 'B' WHEN 'White' THEN 'W' ELSE 'O' END"})

This is indeed "registered with Django":

>>> rs = Record.objects.extra(select={'generalized_race':
... "CASE race WHEN 'Black' THEN'B' WHEN 'White' THEN 'W' ELSE 'O'
END"})
>>> rs[0].generalized_race
u'B'

However, it is correct that although extra(select={'foo': ...})
augments the results with the field foo, it is not a proper
models.Field and can not be referenced in further QuerySet methods.

> 3  fields = ['age', 'sex', 'generalized_race']
> 4  r = r.values(*fields).annotate(Pop = Sum('population'))

Are you sure you need the values() call here? If you really don't
want to select other fields, only() works equally well and retains
the generalized_race pseudo-field:

>>> fields = ['age', 'sex']
>>> rs.only(*fields)[0].generalized_race
u'B'

As for the annotate() clause, it is unclear where the population comes
from and what's the intent. Please clarify.
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---

Aggregate using extra() fields

[Kathleen Benitez]

I am attempting to evaluate some generalization schemes using Django.
In particular, I'd like to create a new field (generalized race), and
then find a total population using the values() function as follows:

1  r = Records.objects
2  r = r.extra(select={'generalized_race: "CASE race WHEN 'Black' THEN
'B' WHEN 'White' THEN 'W' ELSE 'O' END"})
3  fields = ['age', 'sex', 'generalized_race']
4  r = r.values(*fields).annotate(Pop = Sum('population'))

The creation of the extra fields does not seem to be registered in any
way with Django, so it doesn't know what to do in line 4.  Is there
any way around this?  Can someone suggest an alternate approach?

Thanks,
~Kathy

--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---