CODEWITHSUNDEEP
pythonsqldjangopostgresql
Alex
Alex

Reputation: 189

How to filter INSIDE foreign key set?

There are two models

class Question(models.Model):
    text = models.TextField()

class Vote(models.Model):
    cas_id = models.IntegerField()
    question = models.ForeignKey(Question, on_delete=models.CASCADE, related_name='votes')

I want to get ALL questions. Every question's votes set should include only votes with cas_id=123. Some question's votes set may be empty. SQL query looks like:

with user_votes as (
  select *
  from votes
  where cas_id = 123
)
select *
from question q left join user_votes uv on q.id = uv.question_id;

How can I do it via django-ORM in one query?

I tried following.

  1. .filter(votes__cas_id=123) excludes extra rows.
  2. 2 queries and some code work OK.

Upvotes: 0

Views: 50

Answers (1)

Shane
Shane

Reputation: 653

A prefetch with a separate filter in the query should handle this:

Question.objects.all().prefetch_related(Prefetch('votes', queryset=Vote.objects.filter(cas_id=123)))

Upvotes: 1

Related Questions