CODEWITHSUNDEEP
pythondjangopostgresql
Cameron Hyde
Cameron Hyde

Reputation: 118

Django queryset - fetching reverse relations

I have two models with a one-to-many relation through a ForeignKey. At some stage, I have obtained a queryset of the 'one' objects and would like to retrieve, as efficiently as possible, all of the 'many' objects that are associated with them. Here is a minimal example:

class Car(models.Model):
    name = models.CharField(max_length=100)
    description = models.CharField(max_length=1000)

    @classmethod
    def search(cls, keyword):
        return keyword_search_cars(cls, keyword)


class Passenger(models.Model):
    car = models.ForeignKey(Car, on_delete=models.CASCADE)

Now I would like to query the models like so:

# Get all passengers in cars matching term 'truck'
cars = Car.search('truck')
passengers = cars.passenger_set.all()  # Not permitted on querysets!

But the best I can come up with is something like this:

car_ids = Car.search('truck').values_list('id', flat=True)
passengers = Passenger.objects.filter(car_id__in=car_ids)

With hundreds of records in the cars queryset, the __in filter seems like a dumb way to query the database. According to the docs, this does not do a SQL JOIN but instead writes a huge WHERE id IN (1, 2, 3, ... ) which does not seem optimal.

Does anyone know of a better approach for this? Currently my queries take 10+ seconds (with 1M+ records) which means rather poor performance for my application. It will get even slower as the database table grows in length.

For further context, the Cars.search method here can be considered an abstraction for full-text search with the django.contrib.postgres.search module (that's what my application is actually doing).

EDIT: show code for full text search method behind Car.search():

from django.contrib.postgres.search import (
    SearchQuery,
    SearchRank,
    SearchVector,
)

RANK_THRESHOLD = 0.1


def keyword_search_cars(Model, query):
    vector = (
        SearchVector('name', weight='A')
        + SearchVector('description', weight='B')
    )
    query = SearchQuery(query)
    cars_results = (
        Model.objects.annotate(rank=SearchRank(vector, query))
        .filter(rank__gte=RANK_THRESHOLD)
        .order_by('-rank')
    )
    return cars_results

Upvotes: 0

Views: 93

Answers (1)

Niko
Niko

Reputation: 3783

You can use a backward filter lookup with Q objects to search for keyword in both fields of Car model:

Passenger.objects.filter(Q(car__name__contains=keyword) | Q(car__description__contains=keyword))

Upvotes: 1

Related Questions