khvn khvn - 1 year ago 52
Python Question

Simplifying a django ORM query

So here's my nice function:

def is_serviceable(address):
Checks if an address can be serviced by an Employee.
Returns True if address' lat-lng intersects with any of Employees' coverage.
If it doesn't, but address' locality is listed in world District model,
returns False and locality name.
Just returns False if nothing found.

-- address -- django-address Address object. Address should be geocoded (i.e
have latitude and longitude fields correctly filled)
pnt = Point(address.longitude, address.latitude)
employee_exists = Employee.objects.filter(coverage__mpoly__intersects=pnt).exists()
district = District.objects.filter(mpoly__intersects=pnt).first()
if (employee_exists):
return employee_exists
elif district:
return False,
return False

To get what I want I have to perform two database queries, first to Employee table, then to District table. That doesn't seem like a scalable approach. However, since Employee's coverage attribute is actually a many-to-many relation field with District model:

class Employee(models.Model):
coverage = models.ManyToManyField(
verbose_name=_("assigned districts")

class District(models.Model):
mpoly = models.MultiPolygonField()

...I'm pretty sure that there's a way to compress the query into one queryset or, if Django ORM isn't fit for this, a single SQL statement.

I've got no idea where to start digging, though. Any thoughts?

EDIT: Solved thanks to Django's annotation functionality.

Answer Source

You could do an annotation on the count of the employees and then filter on the count of that


You may also want to use .only('name') since that is all you actually use in the district.

Disclaimer: This is untested and I have no idea i this actually will be any faster since exists isn't very intensive