Django right-join on many-to-one relationship

Question

In my system, I have and Account model that has many Locations as follows:

class Account(models.Model):
    # ... also contains billing address data

class Location(models.Model):
    account = models.ForeignKey('Account')
    # ... also contains physical address data

I want to create a search view that allows the user to search for an Account object based on billing address or physical address and display the results in a table with a single Account entry for each associated Location object. I can't do this with a left-join from the Account model; this results in a single entry for each Account object and thus doesn't cover all Location objects that are associated with an Account (I don't care about locations that aren't associated with accounts).

Instead, I want to do this with a right-join from the Location model to the Account model. That way, all accounts are included at least once and once for each location they are associated with, and every location that is associated with an account is included as well.

Is there a way to do this in Django 1.8+?

Edit: Account objects are not required to have associated Location objects, and it may be the case in the future that Location.account is NULL == True for some Location objects.

score 0 · Accepted Answer · answered Jan 23 '16 at 17:25

It turns out that my goals here can much more easily be reached by taking advantage of Django's through declaration for many-to-many relationships. I explicitly define the link table:

class AccountLocation(models.Model):
    account = models.ForeignKey(Account)
    location = models.ForeignKey(Location, null=True, blank=True)

...I then declare the relationship between Account and Location on the Account model:

locations = models.ManyToManyField(Location, through='AccountLocation')

Lastly, I implemented custom save() and delete() logic on theAccountandLocationmodels. TheAccountmodel automatically puts a one-sided entry intoAccountLocationwhenever a newAccountinstance is created, and theLocationmodel removes one-sided entries in the link table when aLocationinstance is created or creates one when the lastLocationlinked to anAccount` is deleted.

This solution satisfies all of my requirements because I can then use AccountLocation as my search table, there will always be at least one entry in that table for every account, and searches can be run on data from both the Account model and the Location model at the same time.

Django doesn't support right-joins, but the same result can be achieved by other means.

Django right-join on many-to-one relationship

1 Answers1