CODEWITHSUNDEEP
pythondjangodjango-models
jyap
jyap

Reputation: 43

How to filter a Django queryset via text field in the latest corresponding entry in a separate model

I have a model where I needed historical data for a couple specific fields, so I put those fields into a separate model with a foreign key relationship.

Something sort of like this:

class DataThing(models.Model):
    # a bunch of fields here...


class DataThingHistory(models.Model):
    datathing_id = models.ForeignKey('DataThing', on_delete=models.CASCADE)
    text_with_history = models.CharField(max_length=500, null=True, blank=True)
    # other similar fields...
    timestamp = models.DateTimeField()

Now I'm trying to filter the former model using a text field in the latest corresponding entry in the latter.

Basically if these were not separate models I'd just try this:

search_results = DataThing.objects.filter(text_with_history__icontains=searchterm)

But I haven't figured out a good way to do this across this one-to-many relationship and using only the entry with the latest timestamp in the latter model, at least by using the Django ORM.

I have an idea of how to do the query I want using raw SQL, but I'd really like to avoid using raw if at all possible.

Upvotes: 4

Views: 3374

Answers (2)

user2390182
user2390182

Reputation: 73460

This solution makes use of distinct(*fields) which is currently only supported by Postgres:

latest_things = DataThingHistory.objects.
    order_by('datathing_id_id', '-timestamp').
    distinct('datathing_id_id')

lt_with_searchterm = DataThingHistory.objects.
    filter(id__in=latest_things, text_with_history__icontains=searchterm)

search_results = DataThing.objects.filter(datathinghistory__in=lt_with_searchterm)

This should result in single db query. I have split the query for readability, but you can nest it into a single statement. Btw, as you might see here, foo_id is not a good name for a ForeignKey field.

Upvotes: 3

Shang Wang
Shang Wang

Reputation: 25539

You would do the same by querying DataThing while referring to DataThingHistory:

search_results = DataThing.objects.filter(datathinghistory__text_with_history__icontains=searchterm)

Check django doc on how to query on reverse relationship.

Edit:

My previous answer is incomplete. In order to search on latest history for each DataThing, you need to annotate on timestamp using Max:

from django.db.models import Max

search_results = search_results.values('field1', 'field2',...).annotate(latest_history=Max('datathinghistory__timestemp'))

This wouldn't give you complete DataThing objects, but you could add as many fields to values as you want.

Upvotes: 0

Related Questions