Enforce JOIN instead of Subquey when using "__in" filter in Django ORM

Question

Task: I want to get all invoices where the project title matches a given string. The Invoice has a foreign key to Project.

The problem: I want to use a function for doing the project search so I can recycle and encapsulate this use-caes. To make this happen I have to use the __in operator instead of project__title__icontains=my_str. This will lead to an inefficient subquery. The later will create an efficient JOIN.

Code examples

I have two custom queryset methods. One lives in Project:

def search_title(self, title: str):
    """
    Searches for projects having the given title or at least parts of it.
    """
    return self.filter(title__icontains=title)

The second one lives in Invoice:

def search_project_title(self, project_title: str):
    return self.filter(project__in=Project.objects.search_title(project_title))

Any ideas how I can tell the Django ORM that I prefer JOINs over Subqueries?

Thx!

willeM_ Van Onsem · Accepted Answer · 2022-10-24 15:04:39Z

0

In that case, you filter with:

def search_project_title(self, project_title: str):
    return self.filter(project__title__icontains=project_title)

answered Oct 24, 2022 at 15:04

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ron Over a year ago

Thx for the input but as I said I want to use the function and NOT use the field of the fk-related model directly.

Collectives™ on Stack Overflow

Enforce JOIN instead of Subquey when using "__in" filter in Django ORM

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related