Combining two querysets into one list with sorting and pagination

Using Django
#1

Hi everyone,

I have two separate models and would like to provide my users with a single search page that would operate over both.

class Person(models.Model):
    name = models.CharField(...)
    ...


class Animal(models.Model):
    name = models.CharField(...)
    ...


# I can write a simplified search view like this:
def search_view(request):
    people = Person.objects.filter(name__icontains=request.GET['q']
    animals = Animal.objects.filter(name__icontains=request.GET['q']

    combined = itertools.chain(people, animals)

    return render(request, 'results.html', {'object_list': combined}

Sorting the results require a bit more work but still seems doable, either by implementing the sorting in Python or with some cleverness when combining the two sorted querysets.

However, I can’t figure out how to handle pagination. I typically use a generic ListView where I just need to declare a paginate_by parameter but that doesn’t work here.

Has anyone ever had to tackle this issue before? Do you know of any third-party app that could make this a bit easier?

Thanks

#2

Your example has

animals = Person.objects.filter(...

but I guess you mean

animals = Animal.objects.filter(...

I’m guessing you can’t use union() easily ? https://docs.djangoproject.com/en/3.0/ref/models/querysets/#union . Its main drawback is that it would convert your Animals to Persons.

Matthew Schinckel’s blog post today covers an interesting pattern of querying multiple models through a database view and turning them back into their correct classes in Python, using Postgres’ JSON field: https://schinckel.net/2020/03/09/querysets-of-various-models/ . There are also two other patterns using proxy models and inheritance.

1 Like
#3

Oops, that was a typo on my part (I fixed it in the original post).

The two querysets are from different models with no common parent so I can’t use union() unfortunately. Changing the models might be a cleaner approach in the long run but that would be way too much work at the moment so I’m looking for alternative approaches (ideally, something that could plug seamlessly into a ListView).

The database view approach seems interesting, I’ll take a look.
Thanks!

#4

You can union() two models with no shared parents, but it’s not necessarily a good idea.

Also in theory you could implement queryset-like class that iterates over the others together, with just enough methods implemented through ducktyping to make ti fit into a ListView / paginator. But I don’t recall seeing such a thing ever.

#5

Once you have your final QuerySet you can override get_queryset method in the ListView and then paginate_by should work after adding the respective template https://docs.djangoproject.com/en/3.0/topics/pagination/#paginating-a-listview

To get the final queryset you can also refer to this(https://stackoverflow.com/questions/431628/how-to-combine-two-or-more-querysets-in-a-django-view) stack overflow thread which seems to be dealing with a similar problem.

#6

django-querysetsequence might be the kind of thing you’re looking for. I’ve not used it though.

#7

@bmispelon may I ask if you did succeed with this challenge?

I have a similar thingy and was going with an idea of creating a separate “index” model with generic relation, containing fields needed for search, but also a JSON field with the data to be displayed. However, this brings a problem of data duplication and maintaining the synchronization between main object and an “index” object.
Furthermore, if there is some extra annotation and filtering logic on one of the models (which is my case) - it is not possible to do proper query optimizations due to the generic relations.

I am now trying this suggestion, thanks for pointing to this package! However, it seems like performance is notably reduced (I wrapped just one model for a test, the outcome became slower).

#8

My original post was in March 2020, I think it’s fair to say a lot has happened since :sweat_smile:

I don’t remember what I ended up doing at all. I don’t have django-querysetsequence in my dependencies today, so I know I didn’t go that route.

Sorry I can’t be of more help. If you do find an interesting solution please share it.
Good luck!

1 Like