Django filter across multiple fields in many staging tables

Question

I have the following models in my django project:

class Video(models.Model): media = models.ForeignKey(Media) class Media(models.Model): title = models.CharField(max_length=255) formats = models.ManyToManyField(Format,through='MediaFormat',related_name='media',blank=True) class Format(models.Model): title = models.CharField(max_length=50) class MediaFormat(models.Model): status = models.IntegerField() format = models.ForeignKey(Format) media = models.ForeignKey(Media) 

Now I want to filter out all the videos with a specific format, and the status code for this format is 10 (ready to use). How can i do this? (assuming f is a format):

 f = Format.objects.get(pk=3) 

I am tempted to use:

 Video.objects.filter(media__formats=f, media__mediaformat__status=10) 

But then it will return all videos that match both of these assumptions:

• a) contain this particular format and
• b) contain any format with status 10

How can I filter only those with this format in status code 10?

Thank you!

Answer 1

Now I want to filter out all the videos that have a specific format, and the status code for this format is 10 (ready to use). How can i do this? (assuming f is a format)

The code you submitted will do exactly what you want:

 Video.objects.filter(media__formats=f, media__mediaformat__status=10) 

This is described in the filter() documentation :

Several parameters are combined via AND in the underlying SQL statement.

Answer 2

You can combine filters for the AND construct.

Video where format f And format status 10

 Video.objects.filter(media__formats=f).filter(media__mediaformat__status=10) 

Answer 3

It probably does not apply to OP, but it can be for others like me who found this thread while looking for the right answer.

Ludwik understood this correctly, but the documentation section that explains all this, as well as how to exclude it, is in the query documentation .

Please note that splitting the filter into two filter calls, for example, proposed by Chris, will give you the exact opposite result: he will look for a video that has media format f and has media format, not necessarily the same multimedia format with status 10.