CODEWITHSUNDEEP
djangodjango-queryset
DynamicViewer
DynamicViewer

Reputation: 77

Exclude from django queryset those results that are referenced in another model's queryset

I'm using Django and have two classes:

class PR(models.Model):
  Jq = models.ForeignKey(JQ)
  user = models.ForeignKey(User)
  (snip)
  def __unicode__(self):
        return self.name

class JQ(models.Model):
  a = models.ForeignKey(A)
  (snip)
  def __unicode__(self):
        return self.name

I want to perform a query where I grab all of the JQs that do not belong to a PR for a specified user. Right now, I'm doing this:

 jq = JQ.objects.filter(somefield=someval)
 pr = PR.objects.filter(user=request.user.id)
 for r in pr:
   jq = jq.exclude(id=r.Jq.id)

That is, I get all the JQs, then grab all PRs that reference a given user. I'm looping over all the PRs and excluding the JQs that are referenced by at least one PR. However, this is horribly inefficient. Any suggestions?

(Btw, in native SQL, I'd probably create a temporary table of that user's PRs and then join that temporary table with the JQs and only keep the rows that don't have a match in the users PRs.)

Thank you for your help!

Upvotes: 4

Views: 2174

Answers (1)

dannyroa
dannyroa

Reputation: 5571

 pr = PR.objects.filter(user=request.user.id).values_list('jq__id', flat=True)
 jq = JQ.objects.filter(somefield=someval).exclude(id__in=pr)

The first line returns a list of ids (e.g. [12, 14, 110]). You then use those ids in the exclude.

Upvotes: 4

Related Questions