I want to get a queryset of all Jobs that are within a given distance to at least one of the many provided locations, order them by the minimum distance, and do not show duplicate jobs.
from django.db import models
from cities.models import City
class Job(models.Model):
title = models.CharField(max_length=255)
cities = models.ManyToManyField(City)
If there is only one point I could do this as:
from django.contrib.gis.db.models.functions import Distance
from django.contrib.gis.geos import Point
point = Point(x, y, srid=4326)
Job.objects.filter(cities__location__dwithin=(point, dist)) \
.annotate(distance=Distance("cities__location", point) \
.order_by('distance')
but when I have many points I build out a Q expression for the filter but am unsure of a clean way to annotate the Min distance of the Job to all points
query = Q()
for point in points:
query |= Q(cities__location__dwithin=(point, dist))
Job.objects.filter(query).annotate(distance=Min(...)).order_by('distance')
FYI using postgres 12.1 with PostGIS extension
query = Q() distances = [] for point in points: query |= Q(cities__location__dwithin=(point, dist)) distances.append(Distance("cities__location", point)) # LEAST requires 2 or more expressions, MIN works for single expression if len(distances) == 1: MIN_FUNC = Min else: MIN_FUNC = Least Job.objects.filter(query).annotate(distance=MIN_FUNC(*distances)).order_by('distance')MIN is an aggregation function that takes a single expression, such as a column name, and reduces multiple inputs to a single output value.LEAST is a conditional expression that works by selecting the smallest value from a list of any number of expressionshttps://docs.djangoproject.com/en/3.0/ref/models/querysets/#min https://docs.djangoproject.com/en/3.0/ref/models/database-functions/#minus