Django ORM - Search Function Across Multiple Models

Question

I am struggling to get my search function to pull in results from multiple models into a single table.

I have a Person Model and a Dates Model:

class Person(models.Model):
personid = models.AutoField()
name = models.CharField()
etc....

class Dates(models.Model):
datesid = models.AutoField()
personid = models.ForeignKey(Person, models.DO_NOTHING)
date_type = models.CharField()
date = models.CharField()
etc....

There will be multiple Dates per personid (many to one) what I am trying to do is return the newest date from Dates when a search is performed on the Person Model - View below:

def search(request):
        if request.method == "POST":
            searched = request.POST.get('searched')
            stripsearched = searched.strip()
            results = Person.objects.filter(
                Q(searchterm1__icontains=stripsearched) | 
                Q(searchterm12__icontains=stripsearched) | 
                Q(searchterm3__contains=stripsearched)
            ).values('personid', 'name', 'address_1', 'address_2', 'address_3', 'code', 'number')

How would I add in the Top 1 newest related Dates.date into this for each Person found in the "results" field ?

View:

{% for Person in results %}
<tr>
  <td>{{ Person.name }}</a></td>
  <td >{{ Person.address_1 }}</td>
  <td >{{ Person.code }}</td>
</tr>
{% endfor %}

maybe this [question](https://stackoverflow.com/questions/9834038/django-order-by-query-set-ascending-and-descending) helps. Perhaps also you need to add `[0]` to get the first obj. — A D, Jan 31 '22 at 10:01
@a-d I can use that to return a date but then it will only return a single date for the entire searched query set, where as the search results could be 200+ different people needing 200+ different dates - hopefully that makes sense. — Harry Waterman, Jan 31 '22 at 10:05

score 0 · Accepted Answer · answered Jan 31 '22 at 10:10

I would try with annotate function. Following your search function modified to add max or min date for a given person

def search(request):
        if request.method == "POST":
            searched = request.POST.get('searched')
            stripsearched = searched.strip()
            results = Person.objects.filter(
                Q(searchterm1__icontains=stripsearched) | 
                Q(searchterm12__icontains=stripsearched) | 
                Q(searchterm3__contains=stripsearched)
            ).annotate(min_date=Min('Dates__date'), max_date=Max('Dates__date')
            ).values('personid', 'name', 'address_1', 'address_2', 'address_3', 'code', 'number', 'min_date', 'max_date')

This will add in the result list 2 new fields min_date and max_date for each person, so you can use it in your template

{% for Person in results %}
<tr>
  <td>{{ Person.name }}</a></td>
  <td >{{ Person.address_1 }}</td>
  <td >{{ Person.code }}</td>
  <td >{{ Person.min_date }}</td>
  <td >{{ Person.max_date }}</td>
</tr>
{% endfor %}

Django ORM - Search Function Across Multiple Models

1 Answers1