Django Many to Many relationship Query Filter

December 15, 2021

class servers(models.Model):
hostname=models.CharField(max_length=100)
ip= models.CharField(max_length=100)
os= models.CharField(max_length=100)

class application(models.Model)
name=models.CharField(max_length=100)
URL= models.CharField(max_length=100)
servers= models.ManyToManyField(servers, blank = True, null=True)

current DB status

3 servers 2 with os as linux and 1 with os as windows

2 applications

Requirement : application can have many servers and each server can be part of many applications also
Need support to create filter only those applications whose os is windows. I tried below but it is returning all three servers.

def viewapp(request,pk)
criterion1 = Q(id=pk)
criterion2 = Q(servers__os__startswith="windows")
prodlist = application.objects.filter(criterion1 & criterion2)

>Solution :

You can filter the many-to-many field as well with a Prefetch object [Django-doc]:

from django.db.models import Prefetch

def viewapp(request,pk):
    prodlist = application.objects.filter(
        id=pk, servers__os__startswith='windows'
    ).prefetch_related(
        Prefetch('servers', Server.objects.filter(os__startswith='windows'))
    )

since you here work with one object however, it is better to work with:

from django.shortcuts import get_object_or_404
from django.db.models import Prefetch

def viewapp(request, pk):
    prod = get_object_or_404(
        application.objects.prefetch_related(
            Prefetch('servers', Server.objects.filter(os__startswith='windows')
        ),
        pk=pk
    )

Here prod is a single application object, not a QuerySet of applications.