I have two models like this:

开发者_如何学C

class AA(models.Model):
    name = models.CharField()
    state = models.IngerField()


class BB(models.Model):
    aa_id = models.ForeignKey(AA)

My question is: How i get all objects AA with state 10 and that are not in BB?

In sql i do something like this:

select * from AA 
where  AA.state = 10 and AA.id not in (select aa_id from BB)

or

select * from AA
left join BB on BB.aa_id = AA.id
where AA.state = 10 and BB.id is null

I know that i can get all AA objects and check one by one if BB has foreign key to it. But is not the right thing to do.

Thanks.

---

Something like this:

AA.objects.filter(state=10, bb=None)

---

The question is old, but the answers and comments was really helpful until 9 years after. Though, even after read the @Daniel Roseman's great answer (thanks man, your answer helped me a lot), so I checked it out a little bit further at the documentation how it works.

You can read below as it is in the documentation:

Lookups that span relationships

Django offers a powerful and intuitive way to “follow” relationships in lookups, taking care of the SQL JOINs for you automatically, behind the scenes. To span a relationship, use the field name of related fields across models, separated by double underscores, until you get to the field you want.

This example retrieves all Entry objects with a Blog whose name is 'Beatles Blog':

>>> Entry.objects.filter(blog__name='Beatles Blog') 

It works backwards, too. To refer to a “reverse” relationship, use the lowercase name of the model.

Using django polls tutorial as example, with Questions and Choices as follows:

# models.py
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)

# Select Questions without related Choices
>>> Question.objects.filter(choice=None)

# Select only Questions with related Choices
>>> Question.objects.exclude(choice=None)

# Select Questions related to Choice with id 1
>>> Question.objects.filter(choice__id=1)