select related and Q objects on already filtered queryset

Question

I have a queryset that I get. Then depending on if certain things are true in my software I further filter the queryset.

venuelist = Venue.objects.filter(approved=True, online=True) # intial queryset

then if things are true or not I filter further.

venuelist = venuelist.filter(venuetype__in=venuetypepklist)

by my understanding __in is an OR operator not an AND

I have a situation in which if something is true, I need to filter further but I need to filter the related sets of a venue object.

I know we can do this with set related. But the thing I am checking could also be true in the actual venue object.

example. I have a fullbuyout capacity option in my venue table.

I also have a capacity option in each of the rooms a venue has.

My filter should work where the venue is not filtered out if either the venues fullbuyout capacity value is greater than or equal to the user input capacity or one of the rooms capacity related to the venue is equal to or greater than the user inputed value.

I am using Q objects and select query however it seems I am not doing it right, or really dont know what I am doing.

Here is what I have so far for the filter above

seatedcapacity = self.request.query_params.get('seatedcapacity',None)
        if seatedcapacity is not None:
           venuelist.filter(Q(select_related('Room").seatedcapacity__gte=seatedcapacity) | Q(fullbuyoutseatedcapacity__gte=seatedcapacity))

but its not working. I assume because of syntax related issues. So my quesiton is what is the correct syntax?

Answer 1

select_related only ensures the related models are also loaded from the database, you can't use them to filter your query. You want to use a Subquery for this, or actually Exists: Here's the documentation.

Assuming you have a ForeignKey relationship between Venue and Room, i.e. each Room has a foreign key to Venue:

rooms = Room.objects.filter(venue=OuterRef('pk'), seatedcapacity__gte=seatedcapacity)
venueslist = venueslist.annotate(has_rooms_with_capacity=Exists(rooms))
venueslist = venueslist.filter(Q(has_rooms_with_capacity=True) | Q(fullbuyoutseatedcapacity__gte=seatedcapacity))