CODEWITHSUNDEEP
pythondjango
khvn
khvn

Reputation: 557

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.

Upvotes: 2

Views: 285

Answers (3)

khvn
khvn

Reputation: 557

Here's a solution based on Sayse's answer:

pnt = Point(address.longitude, address.latitude)
district = District.objects.filter(mpoly__intersects=pnt).annotate(emps=Count('employees')).only('name').first()
if district:
    if district.emps:
        return True
    return False, district.name
else:
    return False

Haven't profiled it yet, but it's only one query instead of two, which is what I've been looking for. Besides, it queries District table first, which means lighter load on Employee table.

Upvotes: 1

Sayse
Sayse

Reputation: 43320

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

Upvotes: 1

mtt2p
mtt2p

Reputation: 1906

Employee objects have access to their related District objects:

x=Employee.objects.all()[0]
print x.district_set.all()

Upvotes: 1

Related Questions