Re: QuerySets with annonate, count() and multiple fields
On Oct 8, 8:36 am, Emily Rodgers wrote: > On 8 Oct, 01:31, Russell Keith-Magee wrote: > > > > > On Thu, Oct 8, 2009 at 1:43 AM, Emily Rodgers > > > 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 %-) > > 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 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() :-) --~--~-~--~~~---~--~~ 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: QuerySets with annonate, count() and multiple fields
On 8 Oct, 01:31, Russell Keith-Magee wrote: > On Thu, Oct 8, 2009 at 1:43 AM, Emily Rodgers > > > > 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 %-) 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 --~--~-~--~~~---~--~~ 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: QuerySets with annonate, count() and multiple fields
On Thu, Oct 8, 2009 at 1:43 AM, Emily Rodgers 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 %-) --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
QuerySets with annonate, count() and multiple fields
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 --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---