SQLAlchemy Many to Many relationship parameter

Question

Am using SQLAlchemy ORM to get the roles that belong to a certain user but am getting the error below

Could not determine join condition between parent/child tables on relationship AppRole.users - there are multiple foreign key paths linking the tables via secondary table 'app_user_role'. 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.

The tables are defined as:

class AppUserRole(Base_Write):
    __tablename__ = 'app_user_role'
    id = Column(Integer, primary_key=True)
    created_at = Column(DateTime, server_default=text('NOW()'), nullable=False)
    created_by = Column(Integer, ForeignKey('app_user.id'), nullable=False)
    is_deleted = Column(Boolean, server_default='0', nullable=False)
    date_deleted = Column(DateTime)
    deleted_by = Column(Integer, ForeignKey('app_user.id'))
    user_id = Column(Integer, ForeignKey('app_user.id'), nullable=False)
    role_id = Column(Integer, ForeignKey('app_role.id'), nullable=False)
    date_assigned = Column(DateTime, nullable=False)


class AppRole(Base_Write):
    __tablename__ = 'app_role'
    id = Column(Integer, primary_key=True)
    created_at = Column(DateTime, server_default=text('NOW()'), nullable=False)
    created_by = Column(Integer)
    is_deleted = Column(Boolean, server_default='0', nullable=False)
    date_deleted = Column(DateTime)
    deleted_by = Column(Integer, ForeignKey('app_user.id'))
    role_classification_id = Column(Integer, ForeignKey('app_role_classification.id'), nullable=False)
    role_code = Column(String(10), nullable=False, unique=True)
    role_name = Column(String(30), nullable=False)

    users = relationship("AppUser", secondary="app_user_role", back_populates="app_role")


class AppUser(Base_Write):
    __tablename__ = 'app_user'
    id = Column(Integer, primary_key=True)
    created_at = Column(DateTime, server_default=text('NOW()'), nullable=False)
    created_by = Column(Integer, ForeignKey('app_user.id'), nullable=False)
    is_deleted = Column(Boolean, server_default='0', nullable=False)
    date_deleted = Column(DateTime)
    deleted_by = Column(Integer, ForeignKey('app_user.id'))
    username = Column(String(50), nullable=False, unique=True)

    roles = relationship("AppRole", secondary="app_user_role", back_populates="app_user")

The query am running is:

for app_user in session.query(AppUser).\
filter(AppUser.is_deleted == False).filter(AppUser.id == user_id):
    #print the names of the roles assigned
    print(app_user.roles.role_name)

The thing is, I have other tables where I've defined other Many to Many relationship parameters and everything works. Here though I keep getting that error. There are other similar questions here on SO but tried different suggestions but no luck. I've tried going by the error being suggested and used a previous question of mine here on SO but even putting the foreign_keys option doesn't seem to work albeit I must admit I was really guessing where to place it.

SQLAlchemy version ==1.0.12, Postgresql 9.5

score 4 · Accepted Answer · answered Apr 14 '16 at 17:11

4

As the error message says, you have multiple foreign keys linking AppRole to AppUser, namely, AppUserRole.user_id, AppUserRole.deleted_by, and AppUserRole.created_by, so you need to specify the foreign_keys argument:

class AppRole(Base):
    ...
    users = relationship("AppUser", secondary="app_user_role", 
                         foreign_keys=[AppUserRole.user_id, AppUserRole.role_id],
                         back_populates="roles")


class AppUser(Base):
    ...
    roles = relationship("AppRole", secondary="app_user_role",
                         foreign_keys=[AppUserRole.user_id, AppUserRole.role_id],
                         back_populates="users")

answered Apr 14 '16 at 17:11

univerio

19,548
3
66
68

This works! Thanks. However, I though `back_populates` normally points to the `table/class/model` where the relationship has been defined meaning in this case I would have thought that for AppRole it would be `back_populates="app_role"` and for the AppUser table it would be `back_populates="app_user"`? – lukik Apr 14 '16 at 21:16
@lukik No, it should be the name of the *relationship* on the opposite side, not the table. – univerio Apr 14 '16 at 22:45

SQLAlchemy Many to Many relationship parameter

1 Answers1