Django search with AND operator many to many relationship

Question

This is my model

class MenuItem(models.Model):
    name = models.CharField(max_length=500, null=False)
    description = models.CharField(max_length=500, null=True)
    image_url = models.CharField(max_length=1000, null=True)
    menu_category = models.ForeignKey(MenuCategory, on_delete=models.CASCADE)

    def __str__(self):
        return f'{self.name}'


class Venue(models.Model):
    name = models.CharField(max_length=500, null=False)

    def __str__(self):
        return f'{self.name}'


class VenueMenu(models.Model):
    venue = models.ForeignKey(Venue, null=False, on_delete=models.CASCADE)
    menu_item = models.ManyToManyField(MenuItem, null=False)

This is my view

@api_view(['GET'])
def search_menu_item(request):
    if request.GET.get('venue') and request.GET.get('search_name'):
        menu_item_filter = Q(menu_item__name__icontains=request.GET.get('search_name'))
        venue_filter = Q(venue__name=request.GET.get('venue').title())
        menu_item_search = VenueMenu.objects.filter(venue_filter & menu_item_filter)
        serializer = VenueMenuSerializer(menu_item_search, many=True)
        return Response(serializer.data)

This is my serializer

class MenuItemSerializer(serializers.ModelSerializer):
    menu_category = MenuCategorySerializer(many=True)

    class Meta:
        model = MenuItem
        fields = '__all__'


class VenueSerializer(serializers.ModelSerializer):
    class Meta:
        model = Venue
        fields = '__all__'


class VenueMenuSerializer(serializers.ModelSerializer):
    menu_item = MenuItemSerializer(many=True)
    venue = VenueSerializer(many=False)

I want to search a single menu item information in a specific venue, as you can see in my query am querying the object but this query is returning me all the menu items associated with that venue including the one which I have searched but I want to have a single menu item which I am searching associated to that venue.

Answer 1

You get the correctly filtered VenueMenus, but the related MenuItems aren't filtered automatically because the filter is for VenueMenu and not MenuItem.

To filter the related MenuItems in the serializer, you'll have to do a filtered prefetch using Prefetch like so:

from django.db.models import Prefetch


menu_item_search = VenueMenu.objects.filter(
    venue_filter & menu_item_filter
).prefetch_related(
    Prefetch(
        'menu_item',
        queryset=MenuItem.objects.filter(name__icontains=request.GET.get('search_name'))
    )
)

Also to improve this further, you can also select the related Venue using select_related to avoid doing a separate query just to get the venue details in the serializer. So all in all:

menu_item_search = VenueMenu.objects.filter(
    venue_filter & menu_item_filter,
).select_related(
    'venue',
).prefetch_related(
    Prefetch(
        'menu_item',
        queryset=MenuItem.objects.filter(name__icontains=request.GET.get('search_name'))
    )
)