Django filter a related model only if a foreign key is not saved as an attribute

user_9645236 0
Viewed 103

I have the following models:

class Route(Model):
    first_stop = models.ForeignKey(
        Stop,
        help_text="The departure point",
    )
    last_stop = models.ForeignKey(
        Stop,
        help_text="The destination point",
    )

    
class Stop(Model):
    location = models.CharField(max_length=100)
    
    route = models.ForeignKey(
        Route,
        related_name="stops",
        related_query_name="stop",
        help_text="the route that this stop belongs to",
    )

a route has at least two stops and references to the first and last stops are kept as attributes.

I'm trying to filter routes that pass through a specific location but not just on the first or last stop.

If the location is only present on its first or last stop then that route should be excluded.

If the location is present on an intermediate stop(not first_stop or last_stop) then that route should be included regardless of whether that location is also present on its first or last stop or not.

if a route has only two stops then it should be excluded.

I made a solution but it's very verbose and ugly and probably inefficient:

routes_to_exclude = []
for route in result: # result is a queryset
    matched_stops = False
    for stop in route.stops.exclude(
        pk__in=(route.first_stop_id, route.last_stop_id)
    ):  # to make sure the desired location isn't only on the first or last stop
        if str(stop.location) == desired_location:
            matched_stops = True
            break
    if matched_stops:
        routes_to_exclude.append(route.pk)
result = result.exclude(pk__in=routes_to_exclude)

Is there a better way to implement this filter?

django-queryset django-orm django-models django
1 Answers

You can .filter(…) [Django-doc] with:

from django.db.models import Q

q1 = Q(count__gt=2)  # more than twice, so also an intermediate
q2 = Q(count__gt=1) & (  # more than one, so at least one start/stop should not match
    ~Q(first_stop=specific_location) |
    ~Q(last_stop=specific_location)
)
q3 = (  # both locations do not match
    ~Q(first_stop=specific_location) &
    ~Q(last_stop=specific_location)
)

Route.objects.filter(
    stop=specific_location
).annotate(
    cnt=Count('stop')
).filter(
    q1 | q2 | q3
)

One can use double underscores (__) to look "through" relations. We thus retrieve Routes for which there is a related stop that is the specific_location, but where it is not the first_stop or last_stop.

user_675790
Related