Opened 18 years ago
Closed 18 years ago
#3587 closed (duplicate)
filter() and order_by()
Reported by: | Owned by: | Adrian Holovaty | |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 0.95 |
Severity: | Keywords: | ||
Cc: | Triage Stage: | Unreviewed | |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description (last modified by )
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).
Change History (7)
comment:1 by , 18 years ago
comment:2 by , 18 years ago
comment:3 by , 18 years ago
Putting the code into braces brings better readability:
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_applicationc adet.sqn.wing.name')
comment:4 by , 18 years ago
Args ... that didn't really improve the readability.
Can you please repost this with proper line endings and with the code in { { { and } } } (remove the spaces between the braces)? Try the preview button before you finally submit.
Regarding order_by
:
I don't know how this folklore about the "table.field
" syntax with the dot goes around -- this might work by chance (but not in all circumstances), but the documented way is "model__field
". Unfortunately, the documented syntax "model__field
" doesn't work, either. Ticket #2076 is about this.
Regarding filter
:
I cannot really make sense of your code in this format, but you seem to have left out what actually went wrong.
I would normally close the ticket, but it seems that Jacob wants to have a look on this.
comment:5 by , 18 years ago
More readable version of the code follows (sorry about that)
Models :
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")
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,})
As for the dot syntax, http://www.djangoproject.com/documentation/db_api/#order-by-fields says :
To order by a field in a different table, add the other table's name and a dot, like so
which lends the idea some legitimacy...
comment:7 by , 18 years ago
Resolution: | → duplicate |
---|---|
Status: | new → closed |
Closing as a duplicate of #2076 regarding order_by()
.
The filter()
side of this ticket isn't very clear as to what (if any) the actual problem is - Chris, I recommend opening a new ticket regarding this which clearly explains a specific bug or enhancement to how filter()
works.
For the order_by() one, I was pointed to the correct syntax :
The documentation implies that selected_Related() was a performance optimisation, but it seems to be required here.