How to restrict Foreign Key choices to another Foreign Key in the same model

Question

I'm making a django application and I'm facing an issue. I am trying to define a model where one ForeignKey would be depending on another ForeignKey.

Description part

My application is about making choices.

So let's say you have a decision to make, a decision has multiple choices, and a choice has a status (because of other constraints).

A status can be used on multiple choices, but a status can be relevant only for one decision, the same one the choice is linked to.

Database schema

It isn't fixed and might be changed if needed :

,____________,                  ,____________,
|            | 1, n        1, 1 |            |
|  Decision  |------------------|   Status   |
|____________|                  |____________|
      |                                |
      | 1, n                           | 1, n
      |                                |
      | 1, 1                           |
,_____|______,                         |
|            | 1, 1                    |
|   Choice   |-------------------------'
|____________|

Code

And here is my current (simplified) (not working) code :

class Decision (models.Model):
    name = models.CharField (max_length = 63)

class Status (models.Model):
    value = models.CharField (max_length = 63)
    decision = models.ForeignKey (Decision)

class Choice (models.Model):
    name = models.CharField (max_length = 63)

    decision = models.ForeignKey (Decision)
    status = models.ForeignKey (Status, limit_choices_to = {'decision' : decision})

The important part here being the limit_choices_to = {'decision' : decision}.

Extra info

I found another SO question (In django, how to limit choices of a foreignfield based on another field in the same model?) dealing about the same question, but the question is becoming old, and the best answer was relying on an external app (django-smart-selects).

I'd rather not have to use something external, and I cannot see why something as simple as a 3-table relationship cannot be solved using only Django !

If someone has any solution, or any suggestion, please tell me.

Answer 1

What you're asking is not possible, at least not within the boundaries you've set (no forms, no external libraries). The status field of your Choice model is a Foreign Key, a relationship between two tables… it doesn't deal with filtering itself, simply put – it doesn't have this feature. And this isn't a django thing, this is a database thing. The Django ORM isn't as far away from the database as you probably think, it's brilliant but it's not magic.

Some of the available solutions are:

• Do it at the FormField level by filtering the queryset
• Use something like django-smart-selects (this does the above)
• Override save on your model, add the check there and throw an error if it fails
• Make status a property and do validation checks on it when it's set

If you go with FormField method as well as overriding save you'll have the benefit of knowing there's no way a Choice can be saved it it violates this constraint, from either the user's end (filling out a form) or the back end (code that calls .save() on a Choice instance.

Answer 2

I'm not familiar with Django, but I if you are trying to solve the "same one the choice is linked to" part of the problem, this is how it can be done at the database level:

Note the usage of identifying relationships, so the DecisionId is migrated down both "branches" and merged at the "bottom". So if a Choice has Status, they both must be linked to the same Decision.

Answer 3

I think that what you need here is a through model, like so:

class Choice (models.Model):
    name = models.CharField (max_length = 63)
    status = models.ForeignKey(Status)
    decision = models.ForeignKey(Decision)

class Status(Models.Model):
    name = models.CharField(max_length=20)

class Decision(models.Model):
    name = models.CharField(max_length = 63)
    choices = models.ManyToManyField(Status, through = "Choice")    

This way, every decision has many choices, each of which has only one status. You could do a query like: my_decision.choices.all() or my_status.decision_set.all()

I suggest you take a look at the documentation for an example on how to use through models

Answer 4

In the following statement decision is neither a callable nor a models.Q object:

status = models.ForeignKey (Status, limit_choices_to = {'decision' : decision})

here is a way to represent your data:

class Decision(models.Model):
    ...

# a status is relevant for only one decision
# there may be more than one Status per Decision.
class Status(Models.Model):
    decision = models.ForeignKey(Decision)

# each choice is linked to one decision and has a status.
class Choice (models.Model):
    status = models.ForeignKey(Status)
    # if status is mandatory, then you can get the decision
    # from status.decision. per se, this fk could be optional.
    decision = models.ForeignKey(Decision)

here is another one:

class Decision(models.Model):
    ...

# a decision has multiple choices
# a choice pertains to only one decision
class Choice (models.Model):
    decision = models.ForeignKey(Decision)

# each status is relevant to one decision
# and may encompass multiple choices.
class Status(Models.Model):
    decision = models.ForeignKey(Decision)
    # problem with this representation is that this allows for
    # a choice to be linked to multiple statuses.
    # this happens when using M2M instead of ForeignKey.
    choices = models.ManyToManyField(Choice)