1

I have these class models:

class Application(Base):
    __tablename__ = 'applications'
    identifier = Column(Integer, primary_key=True)
    name = Column(String)
    description = Column(String)

    level1 = relationship("Teams", secondary='assoc_apps_teams', back_populates='support_1')
    level2 = relationship("Teams", secondary='assoc_apps_teams', back_populates='support_2')


class Teams(Base):
    __tablename__ = 'teams'

    identifier = Column(Integer, primary_key=True)
    name = Column(String)
    description = Column(String)

    support_1 = relationship("Application", secondary='assoc_apps_teams', back_populates='level1')
    support_2 = relationship("Application", secondary='assoc_apps_teams', back_populates='level2')


class AssocAppsTeams(Base, DictSerializable):
    __tablename__ = 'assoc_apps_teams'

    identifier = Column(Integer, primary_key=True)
    apps_id = Column(Integer, ForeignKey("applications.identifier"), nullable=False) 
    support1_id = Column(Integer, ForeignKey("teams.identifier"), nullable=False) 
    support2_id = Column(Integer, ForeignKey("teams.identifier"), nullable=False) 

if __name__ == "__main__":
  app = model.Application("app", "desc")
  session.add(app)    
  session.commit()
  session.close()

Consedering that an application has 2 support levels, each level can have one or more than one team ( each support is a team).

When I run my script I get this error:

sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship Applications.support1 - there are multiple foreign key paths linking the tables via secondary table 'assoc_apps_levels'.  Specify the 'foreign_keys' argument, providing a list of those columns which should be counted as containing a foreign key reference from the secondary table to each of the parent and child tables.

In this case, I have a relationship many to many: an application is managed by two levels of support that are teams, and each support can manage more than one application. How to map this relationship correctly??

Souad
  • 4,856
  • 15
  • 80
  • 140
  • What is the script? – metatoaster Jun 28 '18 at 13:17
  • the script is just some queryies to insert the data to the database. I added an example to the question – Souad Jun 28 '18 at 13:21
  • Possible duplicate of [SQLAlchemy multiple foreign keys in one mapped class to the same primary key](https://stackoverflow.com/questions/22355890/sqlalchemy-multiple-foreign-keys-in-one-mapped-class-to-the-same-primary-key) – metatoaster Jun 28 '18 at 13:26
  • Though in your case, it is _a little bit_ different... like a number of ORM related questions... – metatoaster Jun 28 '18 at 13:27
  • that question does not have the answer to my case. Here I have in an association table two foreign keys to same primary key. – Souad Jun 28 '18 at 13:32
  • You also need to name your attributes in your model classes a lot more clearer to the intent, because I can't tell what `level1` in Application actually should link to, because I don't see the relevant attribute in `AssocAppsTeam` either. – metatoaster Jun 28 '18 at 13:33
  • awesome.................... – Souad Jun 28 '18 at 14:17
  • Your relationship is no more a many to many relationship since the association table combines more than 2 tables. So in this case you should familiarize yourself with the association object relationship pattern: http://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html#association-object. The given duplicate target is valid as well, because that's what you have to do in order to define the join paths *from the assoc object*. – Ilja Everilä Jun 28 '18 at 16:47
  • But. If you really do want to model the associations held in *assoc_apps_teams* as 2 separate **read only** many to many relationships, you could use the fact that SQLAlchemy allows using arbitrary selectables, such as SELECT statements as the "secondary". – Ilja Everilä Jun 28 '18 at 17:02

0 Answers0