CODEWITHSUNDEEP
pythondjangopostgresql
coralvanda
coralvanda

Reputation: 6596

Querying PostgreSQL database (with Django) for only objects with relations

I'm presently learning about Django by working through the official tutorial, and decided to try adding some additional features and tests as suggested here.

I have two models in the DB right now that look basically like this:

class Question(models.Model):
    question_text = models.CharField(max_length=200)
    pub_date = models.DateTimeField('date published')

class Choice(models.Model):
    question = models.ForeignKey(Question, on_delete=models.CASCADE)
    choice_text = models.CharField(max_length=200)
    votes = models.IntegerField(default=0)

I have a view set up to display a list of questions. What I want to do is create a query for the view which will only include question objects that have associated choices. Any question object that has no related choice object should not be included in the result set. This query also ignores questions dated in the future and sorts them, but that part is fine and right out of the tutorial.

This is what I came up with so far. It seems to work, but I can't help thinking I've done it in a really backwards and inefficient way. Is it possible to make one query that handles everything in the database instead of two queries and a list comprehension??

choices = Choice.objects.prefetch_related(
    'question').distinct('question')
question_ids = [x.question.id for x in choices]
return Question.objects.filter(
    id__in=question_ids).filter(
    pub_date__lte=timezone.now()).order_by('-pub_date')[:5]

Upvotes: 1

Views: 642

Answers (1)

AKS
AKS

Reputation: 19831

You can annotate the Question queryset by the counts of Choice objects it is associated with and then exclude the ones with zero count:

from django.db.models import Count
questions = Question.objects \
        .annotate(choice_cnt=Count('choice')) \
        .exclude(choice_cnt=0) \
        .filter(pub_date__lte=timezone.now()) \
        .order_by('-pub_date')[:5]

Upvotes: 1

Related Questions