QuerySets with annonate, count() and multiple fields

[Emily Rodgers]

Hello,

I am a bit stuck on something that I think ought to be really easy (so
I am probably being really stupid!).

I am trying to write a view for a model such that I can pass the view
(via post data) a (or an unknown) number of fields of that model, and
receive back a list of dictionaries of distinct values for those
fields and the number of times that combination of values appears.

So, suppose the model was for holding data about cars, and it had
fields 'manufacturer', 'model', 'fuel_type', 'colour', 'n_of_doors',
etc where the fields had appropriate field types (a mixture of
different field types), I would want to be able to pass the view for
example groupBy=['colour', 'fuel_type'], and it would return a dict of
distinct colour/fuel type combinations and how many cars there are in
the db with those combinations, eg. [{'colour': 'red', 'fuel_type:
'diesel', 'count': 14}, ...].

To me it seems like the kind of thing you would want to use annotate
and Count for, except Count takes one field not multiple fields.

I tried using values() to limit the fields, then python to do the
counting / distinct bit, but it is annoying because I want to be able
to tell it to return the unicode values for foreign keys etc. rather
than ids that would then need to be looked up. Also it is way too
slow.

I am trying to figure a way to do this that is reasonably fast because
I am trying to filter a lot of data. Am I missing something? I haven't
been using python / django much lately so I am probably thinking the
wrong way!

Any help would be appreciated :)

Em

[Russell Keith-Magee]

On Thu, Oct 8, 2009 at 1:43 AM, Emily Rodgers
<emily.kat...@googlemail.com> wrote:
>
> Hello,
>
> I am a bit stuck on something that I think ought to be really easy (so
> I am probably being really stupid!).
>
> I am trying to write a view for a model such that I can pass the view
> (via post data) a (or an unknown) number of fields of that model, and
> receive back a list of dictionaries of distinct values for those
> fields and the number of times that combination of values appears.
>
> So, suppose the model was for holding data about cars, and it had
> fields 'manufacturer', 'model', 'fuel_type', 'colour', 'n_of_doors',
> etc where the fields had appropriate field types (a mixture of
> different field types), I would want to be able to pass the view for
> example groupBy=['colour', 'fuel_type'], and it would return a dict of
> distinct colour/fuel type combinations and how many cars there are in
> the db with those combinations, eg. [{'colour': 'red', 'fuel_type:
> 'diesel', 'count': 14}, ...].
>
> To me it seems like the kind of thing you would want to use annotate
> and Count for, except Count takes one field not multiple fields.

Depending on the exact result you're looking for, this may not be a
problem. The following query may do the job:

Car.objects.values('make','model').annotate(count=Count('id'))

will give you a list of (make, model, count) indicating how many
instances of each make-model pair there are.

The complication here is exactly what you want to count. Are you looking for:
* The number of rows that have every make/model combination?
or
* The number of distinct combinations for every make/model combination?

Consider the following data:

Ford | Explorer | Black
Ford | Explorer | Black
Ford | Explorer | Blue
Ford | Explorer | Red
Dodge | Charger | Red

The query I gave you will return (Ford, Explorer, 4), (Dodge, Charger,
1). However, if you want to collapse the two "black Ford Explorer"
entries and only return a count of 3, you're out of luck. For that you
need to specify multiple columns to the Count(), which Django doesn't
support. If this is what you need, you'll need to fall back on using
raw SQL.

Yours,
Russ Magee %-)

[Emily Rodgers]

Ah your suggestion is exactly what I am after :)

Although, I think if there are foreign keys (or choices for the field)
etc, values() tends to return the id of the value, which means I have
to go figure out which model it relates to, then find that record, and
get the user friendly data (I am returning this using JSON to a ExtJS
script). This starts to make it expensive again.

I will have a play with your suggestion though - thanks :)

Emily

[Emily Rodgers]

On Oct 8, 8:36 am, Emily Rodgers <emily.kate.rodg...@googlemail.com>
wrote:

Realised I was being dumb, and you can follow the foreign keys to get
more useful values by specifying them when you pass the strings to
values() :-)