QuerySets, Q Objects, datetime, and Far Too Much Context
|Django Django1.3 PostgreSQL||2012-08-07|
Currently I'm working on a project that involves collections of activities that take place over the course of 12 weeks. I'm using Postgres, Django 1.3, and the DateTimeField class (which translates to the timestamptz type in Postgres) to handle dates in my models. (A note: Although it creates a few complications for me, I opted to use DateTimeField instead of DateField because there will be use cases that call for a time value, just not the one I'm about to outline. If you don't need times, use DateField - it will make your life and your QuerySets a lot simpler.)
I'm about to get into far too much detail here, so feel free to skip to the interesting bit.
The Activity model referenced below doesn't matter for this illustration, but the Date and Dateset models do:
The Date objects are then grouped into Datesets - the four activities that occur on Week 1/Day 1, Week 1/Day 2, and so on:
The Dates are created at the beginning of the 12-week program - in one of my view methods, I generate the Dates like so:
Then the Dates are grouped into Datesets:
So I have a total of 60 Datesets - one for each day of the program (12 weeks, 5 days per week). And each Dateset contains the four activities to be completed for that day.
Phew. Okay, now here's where I get to the point.
These Datesets can be marked as completed or not - the idea is that as you complete each day's set of activities, you check it off.
What I want to display on the page are a) any Datesets from the current day or previous days that are not yet marked as complete and b) the current day's Dateset, even after it has been marked as complete. Also, since I may be returning more than one Dateset (in case there are incomplete Datesets from previous days), I want to show the newest one first (so the older ones are pushed to the bottom of the page). Oh, and these Datesets must be unique to the current user.
Not only will this call for a compound condition in my query, but I'm also going to be working with datetime objects which are notoriously tricky.
I've imported these modules (timedelta was used above, in the original creation of the Date objects):
The QuerySet uses Django's Q objects. Remember that, when using Q objects, those conditions must be passed in first, before any other filter. I've defined two conditions, separated by a pipe symbol (|) to indicate 'OR' (to link the two conditions with 'AND' you would instead separate them with a comma).
In the first condition, the activity_date must be less than or equal to today's date and 'complete' must be False.
In the second condition, the activity_date equals today's date, but the value for 'complete' is irrelevant - I want today's records regardless of the whether they're complete or not.
Finally, the result set is limited to those records belonging to request.user, and the ordering is set - activity_dates descending from newest to oldest.
This evaluates to:
Another neat trick I discovered along the way - once you've defined your QuerySet, you can print the SQL using its query attribute, especially helpful if you're just beginning to build using runserver: