Limit django queryset by another related table

Lets say I have 2 django models like this:

class Spam(models.Model):
    somefield = models.CharField()

class开发者_运维问答 Eggs(models.Model):
    parent_spam = models.ForeignKey(Spam)
    child_spam = models.ForeignKey(Spam)

Given the input of a "Spam" object, how would the django query looks like that:

• Limits this query based on the parent_spam field in the "Eggs" table
• Gives me the corresponding child_spam field
• And returns a set of "Spam" objects

In SQL:

SELECT * FROM Spam WHERE id IN (SELECT child_spam FROM Eggs WHERE parent_spam = 'input_id')

I know this is only an example, but this model setup doesn't actually validate as it is - you can't have two separate ForeignKeys pointing at the same model without specifying a related_name. So, assuming the related names are egg_parent and egg_child respectively, and your existing Spam object is called my_spam, this would do it:

my_spam.egg_parent.child_spam.all()

or

Spam.objects.filter(egg_child__parent_spam=my_spam)

Even better, define a ManyToManyField('self') on the Spam model, which handles all this for you, then you would do:

my_spam.other_spams.all()

According to your sql code you need something like this

Spam.objects.filter(id__in= \
    Eggs.objects.values_list('child_spam').filter(parent_spam='input_id'))