Django RAW SQL query how to pass date comparison-CodePudding

I am working with Django Raw SQL as I want to build a relatively complex query,

As part of it, I have a case statement that says 'if the date on the record is < today then 1 else 0 - this works.

overdue_phases = task.objects.filter(orgid=orgid, taskid=taskidx).raw('''
    
     SELECT '1' as id, case when research_due < "2022-12-01" AND research_status != "done" then 1 else 0 end as count     
     from app_task where taskid = "'''   taskidx   '''"''')
                   
overdue_phases = task.objects.filter(orgid=orgid, taskid=taskidx).raw('''

 SELECT '1' as id, case when research_due < "2022-12-01" AND research_status != "done" then 1 else 0 end as count     
 from app_task where taskid = "'''   taskidx   '''"''')

However, when I want to swap the hard coded date for TODAY() I can't make it work. I have tried passing a python date variable into the script (td = datetime.date.today()), but it doesn't return the right results!

Can anyone advise me please?

CodePudding user response：

There is no need to do this. You can use condition expressions ^[Django-doc]:

from django.db.models import Case, Q, Value, When

overdue_phases = task.objects.filter(orgid=orgid, taskid=taskidx).annotate(
    count=Case(
        When(
            ~Q(research_status='done'),
            research_due__lt='2022-12-01',
            then=Value(1),
        ),
        default=Value(0),
    )
)

Or if a boolean is sufficient as well:

from django.db.models import Q

overdue_phases = task.objects.filter(orgid=orgid, taskid=taskidx).annotate(
    count=~Q(research_status='done') & Q(research_due__lt='2012-12-01')
)

or for older versions of Django with an ExpressionWrapper ^[Django-doc]:

from django.db.models import BooleanField, ExpressionWrapper, Q

overdue_phases = task.objects.filter(orgid=orgid, taskid=taskidx).annotate(
    count=ExpressionWrapper(
        ~Q(research_status='done') & Q(research_due__lt='2012-12-01'),
        output_field=BooleanField(),
    )
)

You can replace '2022-12-01' with date.today().