id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
3587	filter() and order_by()	chris_brand@…	Adrian Holovaty	"I suspect that this should be multiple tickets, but here goes...

This is to document where I had problems getting filter() and order_by() to do what I want. I'm using MySQL.
Excerpts from my model :
{{{
#!python
class Cadet(models.Model):
    sqn = models.ForeignKey(Squadron)
    enrollment_date = models.DateField(help_text=""YYYY-MM-DD"")
    date_of_birth = models.DateField(help_text=""YYYY-MM-DD"")

class Camp(models.Model):
    name = models.CharField(maxlength=40)
    min_training_level = models.PositiveIntegerField(""Minimum training level"", default=0)
    min_age = models.FloatField(""Minimum age"", max_digits=3, decimal_places=1, default=0)

class Application(models.Model):
    cadet = models.ForeignKey(Cadet, edit_inline=models.STACKED, num_extra_on_change=1, num_in_admin=1)
    year = models.IntegerField(maxlength=4, core=True, help_text=""4-digit year"", validator_list=[year_validator])
    camp = models.ForeignKey(Camp, core=True)
    training_level = models.PositiveIntegerField(""current training level"")
}}}

The only places I can find where I couldn't get filtering to work are where I'm doing something that is probably too complex to do with SQL (although I'm no SQL expert).

Here's the function I ended up with after trying to get it going with
filter() :

{{{
#!python

def exception_age(request, year_filter):
    """"""Lists cadets that might not be the right age for the course they applied for.""""""
    this_june = date(int(year_filter), 6, 1)
    this_sept = date(int(year_filter), 9, 1)
    apps = list(Application.objects.filter(year=year_filter))
    # filter out any that are definitely the right age
    apps = [a for a in apps if (a.cadet.age_on(this_june) <
float(a.camp.min_age)) or (a.cadet.ages_out() < this_sept)]
}}}

I don't have the filter() versions that I tried along the way, but it was based on comparing the application.cadet.date_of_birth field.

This one involves comparing the values in two tables :
{{{
#!python

def exception_eligibility(request, year_filter):
    """"""Lists cadets not in the right level for the course they applied for.""""""
    apps = list(Application.objects.filter(year=year_filter))
    # filter out any that are in the right level
    apps = [a for a in apps if a.training_level < a.camp.min_training_level]
    # Sort the list by squadron number
    apps.sort(key=sqn_of_app);
    return render_to_response('camps/exception_eligibility.html',
{'object_list' : apps,})
}}}

and this one requires doing a calculation on the difference between two fields (cadet.date_of_birth and cadet.enrollment_date) :

{{{
#!python

def exception_enrollment(request, year_filter):
    """"""Lists cadets enrolled too young who've applied this year.""""""
    apps = list(Application.objects.filter(year=year_filter))
    # filter out any that joined old enough
    cdts = [a.cadet for a in apps if a.cadet.age_at_enrollment() < 12]
    # Sort the list by squadron number
    cdts.sort(key=sqn_of_cdt);
    return render_to_response('camps/exception_enrollment.html',
{'object_list' : cdts,})
}}}

I do  have examples of where order_by doesn't work. Here's one :

{{{
#!python

def wing(app):
    return app.cadet.sqn.wing.name

def camp_csv(request, year_filter, object_id):
    [...]
    app_list = list(camp.application_set.filter(year=year_filter))
    # Sort by wing
    app_list.sort(key=wing)
#    app_list =
camp.application_set.filter(year=year_filter).order_by('camps_application__c
adet.sqn.wing.name')
    [...]
}}}

You can see where I've commented-out what I wanted to do. The commented-out version gives :
{{{
Exception Type:  	OperationalError
Exception Value: 	(1054, ""Unknown column
'camps_application__cadet.sqn.wing.name' in 'order clause'"")
}}}

This is following a series of foreign keys (application belongs to cadet, belongs to sqn, belongs to wing).
"		closed	Database layer (models, ORM)	0.95		duplicate			Unreviewed	0	0	0	0	0	0