khvn khvn - 6 months ago 10
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, district.name
else:
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(
District,
related_name="employees",
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

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

District.objects.filter(mpoly__intersects=pnt).annotate(emps=Count('employees')).filter(emps__gt=0).first()

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