Re: Efficient Grouping of Database Query

On Monday, December 27, 2010 7:33:42 PM UTC, greg wrote:

Hi, I'm trying to render a page with a table of records.

Each entry in the table is a 'Lift' which I need grouped by
weight_class and then only the top result for each lift shown.

So far example if I have something like this in the database:

weight_class | event             | weight
132               | bench press  | 156
132               | bench press  | 165
132               | deadlift          | 315
132               | squat            | 278
148               | bench press  | 225
148               | bench press  | 208
148               | squat            | 315

I want to render a table on the web-page like:

132lb. Weight Class
132               | bench press  | 165
132               | deadlift          | 315
132               | squat            | 278

148lb. Weight Class
148              | bench press    | 225
148              | squat              | 315

Obviously I can hack together a pretty ugly method to do this
(separate queries for each weight class & lift) but since that is a
maintenance nightmare... I was wondering if there is an elegant way to
do this using some of django's features?

Is my best bet to just do one large query for all record setting lifts
and then sort them into a dictionary?

Thanks,

Greg Kerr

You just need to use the aggregation features:

http://docs.djangoproject.com/en/1.2/topics/db/aggregation/

lifts = Lift.objects.values('weight_class', 'event').aggregate(Max('weight'))

This will give you a list of all weight_class and events, with the max weight for each.

--

DR.

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