#3587: filter() and order_by()
---------------------------------------+------------------------------------
Reporter: [EMAIL PROTECTED] | Owner: adrian
Status: new | Component: Database wrapper
Version: 0.95 | Resolution:
Keywords: | Stage: Unreviewed
Has_patch: 0 | Needs_docs: 0
Needs_tests: 0 | Needs_better_patch: 0
---------------------------------------+------------------------------------
Old description:
> 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 :
> 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() :
> 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 :
> 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) :
> 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 :
> 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).
New description:
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).
Comment (by mtredinnick):
Fixed formatting in summary.
--
Ticket URL: <http://code.djangoproject.com/ticket/3587#comment:6>
Django Code <http://code.djangoproject.com/>
The web framework for perfectionists with deadlines
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/django-updates?hl=en
-~----------~----~----~----~------~----~------~--~---
