Making queries using F() and timedelta at django
I have the following model:
class Process(models.Model):
title = models.Charfield(max_length=255)
date_up = models.DateTimeField(auto_now_add=True)
days_activation = models.PositiveSmallIntegerField(default=0)
Now I need to query for all Process
objects that have expired, according to their value of days_activation
.
I tried
from datetime import datetime, timedelta
Process.ob开发者_如何学Pythonjects.filter(date_up__lte=datetime.now()-timedelta(days=F('days_activation')))
and received the following error message:
TypeError: unsupported type for timedelta days component: F
I can of course do it in Python:
filter (lambda x: x.date_up<=datetime.now() - timedelta(days=x.days_activation),
Process.objects.all ()),
but I really need to produce a django.db.models.query.QuerySet
.
7 days == 1 day * 7
F
is deep-black Django magic and the objects that encounter it
must belong to the appropriate magical circles to handle it.
In your case, django.db.models.query.filter
knows about F
, but datetime.timedelta
does not.
Therefore, you need to keep the F
out of the timedelta
argument list.
Fortunately, multiplication of timedelta * int
is supported by F
,
so the following can work:
Process.objects.filter(date_up__lte=datetime.now()-timedelta(days=1)*F('days_activation'))
As it turns out, this will work with PostgreSQL, but will not work with SQlite (for which Django 1.11 only supports +
and -
for timedelta
,
perhaps because of a corresponding SQlite limitation).
You are mixing two layers: run-time layer and the database layer. F
function is just a helper which allows you to build slightly more complex queries with django ORM. You are using timedelta
and F
together and expecting that django ORM will be smart enough to convert these things to raw SQL, but it can't, as I see. Maybe I am wrong and do not know something about django ORM.
Anyway, you can rewrite you ORM call with extra extra and build the WHERE clause manually using native SQL functions which equals to datetime.now()
and timedelta
.
You have to extend Aggregate. Do like below:
from django.db import models as DM
class BaseSQL(object):
function = 'DATE_SUB'
template = '%(function)s(NOW(), interval %(expressions)s day)'
class DurationAgr(BaseSQL, DM.Aggregate):
def __init__(self, expression, **extra):
super(DurationAgr, self).__init__(
expression,
output_field=DM.DateTimeField(),
**extra
)
Process.objects.filter(date_up__lte=DurationAgr('days_activation'))
Hopefully, It will work for you. :)
I tried to use solution by Lutz Prechelt above, but got MySQL syntax error. It's because we can't perform arithmetic operations with INTERVAL in MySQL.
So, for MySQL my solution is create a custom DB function:
class MysqlSubDate(Func):
function = 'SUBDATE'
output_field = DateField()
Example of usage:
.annotate(remainded_days=MysqlSubDate('end_datetime', F('days_activation')))
Also you can use timedelta, it will be converted into INTERVAL
.annotate(remainded_days=MysqlSubDate('end_datetime', datetime.timedelta(days=10)))
精彩评论