CODEWITHSUNDEEP
pythondjango
user764357
user764357

Reputation:

Finding events that happened on the same day in Django

I have a Person model and people can have jobs:

class Person(models.Model):
    name = TextField()
    birth_date = DateField()

class Job(models.Model):
    name = TextField()
    start_date = DateField()
    person = ForeignKey('Person')

I want to find people who started work on their birthday.

Now, if I wanted to find people who started work on their exact birthday, I'd use:

from django.db.models import F
Person.objects.filter(birth_date=F('job__start_date'))

But this returns no one, because no one is assigned a job the they are born (hopefully).

I can find everyone who started work on April 1st:

Person.objects.filter(birth_date__month=4, birth_date__day=1)

But, if I try to combine this find people who started work during their birthday month:

from django.db.models import F
Person.objects.filter(birth_date__month=F('job__start_date__month'))

I get:

FieldError at /data/explorer/
Cannot resolve keyword u'month' into field. Join on 'start_date' not permitted

How can I find people who started work on their birthday?

Upvotes: 3

Views: 174

Answers (1)

ryuusenshi
ryuusenshi

Reputation: 1986

Lookups on Date(Time) fields in F expressions are not currently supported in django. There is still a way, hacky as it may be.

from django.db import connection
from django.db.models import F

start_month = connection.ops.date_extract_sql('month', '%s.start_date' % Job._meta.db_table)
start_day = connection.ops.date_extract_sql('day', '%s.start_date' % Job._meta.db_table)
birth_month = connection.ops.date_extract_sql('month', 'birth_date')
birth_day = connection.ops.date_extract_sql('day', 'birth_date')

# to trigger the join and make the job table available
# otherwise the next query will fail (there's probably a less hacky way to do this)
persons = Person.objects.filter(job=F('job__pk'))

persons.extra(
    {
        'start_month': start_month,
        'start_day': start_day,
        'birth_month': birth_month,
        'birth_day': birth_day
    },
    where=['start_day=birth_day AND start_month=birth_month']
)

Upvotes: 1

Related Questions