Search code examples
pythonormsqlalchemypython-elixir

Python SQLAlchemy/Elixer Question

I am trying to define a SQLAlchemy/Elixer model that can describe the following relationship. I have an SSP table, which has multiple Foreign Keys to the POC table. I've defined the ManyToOne relationships correctly within the SSP object (allowing me to SSP.get(1).action.first_name correctly). What I would also like to add is the other side of this relationship, where I can perform something like POC.get(1).csa and return a list of SSP objects in which this POC is defined as the idPOCCSA.

I know this would be best for a polymorphic association but I really can not change the DB schema at all (creating a new poc2ssp table with a column for type of association).

class POC(Entity):
  using_options(tablename = 'poc', autoload = True)

  # These two line visually display my "issue":
  # csa = OneToMany('SSP')
  # action = OneToMany('SSP')


class SSP(Entity):
  '''
  Many to One Relationships:
  - csa: ssp.idPOCCSA = poc.id
  - action: ssp.idPOCAction = poc.id
  - super: ssp.idSuper = poc.id
  '''
  using_options(tablename = 'spp', autoload = True)

  csa = ManyToOne('POC', colname = 'idPOCCSA')
  action = ManyToOne('POC', colname = 'idPOCAction')
  super = ManyToOne('POC', colname = 'idPOCSuper')

Any ideas to accomplish this? The Elixer FAQ has a good example utilizing the primaryjoin and foreign_keys parameters but I can't find them in the documentation. I was kind of hoping OneToMany() just supported a colname parameter like ManyToOne() does. Something a bit less verbose.

Solution

  • Try the following:

    class POC(Entity):
  # ...
  #declare the one-to-many relationships
  csas = OneToMany('SSP')
  actions = OneToMany('SSP')
  # ...

class SSP(Entity):
  # ...
  #Tell Elixir how to disambiguate POC/SSP relationships by specifying
  #the inverse explicitly.
  csa = ManyToOne('POC', colname = 'idPOCCSA', inverse='csas')
  action = ManyToOne('POC', colname = 'idPOCAction', inverse='actions')
  # ...