CODEWITHSUNDEEP
djangodjango-models
Colin
Colin

Reputation: 3752

a fairly complex django query

I've got 

class Supplier(Model) : 
   pass

class Customer(Model) : 
   pass

class Dock(Model) : 
   pass

class SupplierDockAccess(Model) : 
   supplier = ForeignKey(Supplier)
   dock = ForeignKey(Dock)

class SupplierCustomerAccess(Model):
   supplier = ForeignKey(Supplier)
   customer = ForeignKey(Customer)

I have an instance of Customer, and I'd like to get all Docks that the customer has access to. Customers have access to Suppliers via SupplierCustomerAccess, and Suppliers have access to Docks via SupplierDockAccess. I can do it like so:

# get the suppliers the customer has access to
supplier_customer_accesses = SupplierCustomerAccess.objects.filter(customer=customer)
suppliers = [s.supplier for s in supplier_customer_accesses]

# get the docks those suppliers have access to
supplier_dock_accesses = SupplierDockAccess.objects.filter(supplier__in=suppliers)
docks = [s.dock for s in supplier_dock_accesses]

... but then the resulting list of docks contains duplicates, and I really think it oughtta be possible to do it in one go. Anyone feel like demonstrating some mighty django-fu?

Upvotes: 0

Views: 136

Answers (2)

Jack M.
Jack M.

Reputation: 32070

Easiest way I can think of to do this is a combination of ManyToManyFields and a custom QuerySet/Manager.

from django.db import models
class CustomQuerySetManager(models.Manager):
    """
        Class for making QuerySet methods available on result set
        or through the objects manager.
    """
    def get_query_set(self):   
        return self.model.QuerySet(self.model) 
    def __getattr__(self, attr, *args): 
        try:                   
             return getattr(self.__class__, attr, *args)
        except AttributeError:
             return getattr(self.get_query_set(), attr, *args)

class Customer(models.Model):
   suppliers = models.ManyToManyField(through=SupplierCustomerAccess)
   objects = CustomQuerySetManager()
   class QuerySet(QuerySet):
       def docks(self):
           return Dock.objects.filter(
                   supplierdockaccess__supplier__in=self.suppliers
               ).distinct()
   ...
class Supplier(models.Model):
   docks = models.ManyToManyField(through=SupplierDockAccess)
   ...

You should see only one or two hits to the database (depending on if you used select_related when you got your customer), and your code is insanely clean:

docks = customer.docks()
suppliers = customer.suppliers.all()
...

Upvotes: 2

Colin
Colin

Reputation: 3752

Alright, I figured it out. One of those things where talking about it seems to do the trick:

docks = Dock.objects.filter(supplierdockaccess__supplier__suppliercustomeraccess__customer=customer).distinct()

...and looking at the sql, it does indeed do it in one big join. Nice. Sorry about answering my own question.

Upvotes: 2

Related Questions