Django sorting by date(day)

Question

I want to sort models by day first and then by score, meaning I'd like to see the the highest scoring Articles in each day.

class Article(models.Model):

date_modified = models.DateTimeField(blank=True, null=True)
score = models.DecimalField(max_digits=5, decimal_places=3, blank=True, null=True)

This answer Django Datetime Field Query - Order by Time/Hour suggests that I use '__day' with my date_modified as in:

Article.objects.filter().order_by('-date_modified__day', '-score')
FieldError: Cannot resolve keyword 'day' into field. Join on 'date_modified' not permitted.

However I get the same error as in the post, so I'm not even sure it should work this way.

I found other answers django order by date in datetime / extract date from datetime using .extra:

Article.objects.filter().extra(select={"day_mod": "strftime('%d', date_modified)"}, order_by=['-day_mod', '-score'])

This works for filtering with no other conditions, but if I apply a condition on the filter such as a category:

Article.objects.filter(category = 'Art').extra(select={'day_mod': "strftime('%d', date_modified)"}, order_by=['-day_mod', '-score'])

I get this error:

File "/home/mykolas/anaconda2/lib/python2.7/site-packages/IPython/core/formatters.py", line 699, in __call__
printer.pretty(obj)

File "/home/mykolas/anaconda2/lib/python2.7/site-packages/IPython/lib/pretty.py", line 383, in pretty
return _default_pprint(obj, self, cycle)

File "/home/mykolas/anaconda2/lib/python2.7/site-packages/IPython/lib/pretty.py", line 503, in _default_pprint
_repr_pprint(obj, p, cycle)

File "/home/mykolas/anaconda2/lib/python2.7/site-packages/IPython/lib/pretty.py", line 694, in _repr_pprint
output = repr(obj)

File "/home/mykolas/lenv/lib/python2.7/site-packages/django/db/models/query.py", line 234, in __repr__
data = list(self[:REPR_OUTPUT_SIZE + 1])

File "/home/mykolas/lenv/lib/python2.7/site-packages/django/db/models/query.py", line 258, in __iter__
self._fetch_all()

File "/home/mykolas/lenv/lib/python2.7/site-packages/django/db/models/query.py", line 1074, in _fetch_all
self._result_cache = list(self.iterator())

File "/home/mykolas/lenv/lib/python2.7/site-packages/django/db/models/query.py", line 52, in __iter__
results = compiler.execute_sql()

File "/home/mykolas/lenv/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 848, in execute_sql
cursor.execute(sql, params)

File "/home/mykolas/lenv/lib/python2.7/site-packages/django/db/backends/utils.py", line 83, in execute
sql = self.db.ops.last_executed_query(self.cursor, sql, params)

File "/home/mykolas/lenv/lib/python2.7/site-packages/django/db/backends/sqlite3/operations.py", line 146, in last_executed_query
return sql % params

TypeError: %d format: a number is required, not unicode

Don't really know what's going on here, help would be appreciated.

Answer 1

from django.db.models import DateTimeField
from django.db.models.functions import Trunc

Article.objects.order_by(
    Trunc('date_modified', 'date', output_field=DateTimeField()).desc(),
    '-score')

• Trunc() (Django 1.10)
• order_by()

Answer 2

If you're using Django >= 1.8 you can use a Func expression. The problem you're experiencing is that the % notation is passed directly to the database adapter, which is trying to replace the % with the relevant parameters.

You can use it like this:

from django.db.models import F, Func, Value

Article.objects.annotate(
    day_mod=Func(Value('%d'), F('date_modified'), 
    function='strftime'
).order_by('-day_mod', '-score')

This should (theoretically, I haven't tested it) end up with a SQL query like this:

SELECT 
  ...
  strftime('%d', "article"."date_modified") AS "day_mod"
FROM "article"
  ...
ORDER BY "day_mod" DESC, "score" DESC

However, I suspect you'll need to add the year and the month to the strftime, otherwise you'll end up with the articles at the beginning of a month being buried by older articles that happened at the end of previous months.

It should also be noted that the strftime function is not supported by MySQL or PostgreSQL. As far as I can tell it's only supported by SQLite, which shouldn't be used in production.

Unfortunately there doesn't seem to be a standard for datetime formatting in SQL. MySQL seems to use DATE_FORMAT(date, format) and PostgreSQL uses to_char(date, format).

If you want to support both SQLite and another DB, you can write a custom expression with the relevant as_sqlite and as_<db> methods that will format your datetimes, but it might be a bit difficult, as not all the DBs use the same formatting strings.

Your best bet is probably to just cast the datetime to date, i.e. CAST (date_modified AS DATE). This ought to work in most flavours of SQL. Simplest way I can come up with is:

from django.db.models import DateField, Expression, F

class CastToDate(Expression):
    template = 'CAST( %(expressions)s AS DATE )'

    def __init__(self, expressions, output_field=None, **extra):
        output_field = output_field or DateField()
        super(CastToDate, self).__init__(self, expressions, output_field, **extra)
        if len(expressions) != 1:
            raise ValueError('expressions must have exactly 1 element')

Articles.objects.annotate(
    day_mod=CastToDate(F('date_modified'))).order_by('-day_mod', '-score')