smart-select ChainedForeignKey in admin

Hej! :slight_smile:

I have 5 models which are connected hierarchical with each other.
Section → division → group → class → wz

one section can have multiple divisions, but one division can only have one section (and so on). Therefor I have ForeignKeys set:

# models.py
class NaceSection(models.Model):
    code = models.CharField(max_length=1, unique=True)
    description_english = models.CharField(max_length=500)


class NaceDivision(models.Model):
    code = models.CharField(max_length=2, unique=True)
    nace_section = models.ForeignKey(NaceSection, on_delete=models.CASCADE, related_name="nace_section")
    description_english = models.CharField(max_length=500)


class NaceGroup(models.Model):
    nace_division = models.ForeignKey(NaceDivision, on_delete=models.CASCADE, related_name="nace_division")
    code = models.CharField(max_length=4, unique=True)
    description_english = models.CharField(max_length=500)
 

class NaceClass(models.Model):
    nace_group = models.ForeignKey(NaceGroup, on_delete=models.CASCADE, related_name="nace_group")
    code = models.CharField(max_length=5, unique=True)
    description_english = models.CharField(max_length=500)
  

class WZ(models.Model):
    nace_class = models.ForeignKey(NaceClass, on_delete=models.PROTECT, related_name="wz")
    code = models.CharField(max_length=7, unique=True)
    description_english = models.CharField(max_length=500)

I than have a model where all those are integrated as M2M fields with a dropdown option.
My goal is to only get the divisions which are in the already selected section in the admin area. (and so on)

I tried smart-select ChainedForeignKey:

# models.py

class Institution(models.Model):
    nace_sections = models.ManyToManyField(
        NaceSection,
        related_name="nace_sections"
    )
    nace_divisions = ChainedForeignKey(
        NaceDivision,
        chained_field="nace_sections",
        chained_model_field='nace_sections',
        blank=True,
    )
     nace_group = ChainedForeignKey(
        NaceGroup,
        chained_field="nace_divisions",
        chained_model_field='nace_divisions',
        blank=True,
    )

The organisation and dropdown in the admin area do not change at all and my view with a table of all my results tells me ('42S22', "[42S22] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid column name 'nace_divisions_id'. (207) (SQLExecDirectW)")

With the ChainedManyToManyField nothing at all happens. Does anybody know what’s going wrong?
Any help appreciated! :slight_smile:

Hi piahh,

I’m not familiar with this library, but I might be able to help.

  1. If there’s more to the error (stack trace, message, etc) please include it.
  2. Do you know what SQL is being generated that’s throwing the error?
  3. Is it possible to figure out what table it’s trying to select nace_divisions_id on?
  4. Have you been able to use ChainedForeignKey successfully with MSSQL?

Additonally, you may have better luck opening an issue on the repo.

Hej Tim!

thanks for your thoughts.

  1. (‘42S22’, “[42S22] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid column name ‘nace_sections_id’. (207) (SQLExecDirectW); [42S22] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid column name ‘nace_divisions_id’. (207); [42S22] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid column name ‘nace_groups_id’. (207); [42S22] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid column name ‘nace_classes_id’. (207); [42S22] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid column name ‘wz_id’. (207); [42S22] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)”)

this is my error message.

  1. I have no idea. The error occurs when saving in the editor area. but the hierachical dropdown is not working at all.

  2. I don’t know. there is no such column in any table

  3. I never used it before.

thanks for the repo tipp!

I’m sorry, but I’m not able to glean any helpful information from the additional error content. It is super weird that it’s calling out a number of columns for not existing: nace_sections_id, nace_divisions_id, nace_groups_id, nace_classes_id, wz_id. I wonder if there’s a setup step missing.

probably, but I have no idea either :smiley:
and from my understanding is my code part exactly like in the documentation.

Just trying to cover all the bases here - have you done a makemigrations / migrate after adding this?

Also, what columns do exist in the Institution model described here?