SQLAlchemy many to many dynamic lazyload not returning AppenderQuery

Question

I'm trying to get a grip on the backref lazyload dynamic feature of SQLAlchemy ORM.

I have 3 tables and two link tables.

course_members = Table('course_members', Base.metadata,
            Column('user_id', Integer, ForeignKey('users.id')),
            Column('course_id', Integer, ForeignKey('courses.id'))
            )

course_roles = Table('course_roles', Base.metadata,
              Column('role_id', Integer, ForeignKey('roles.id')),
              Column('course_id', Integer, ForeignKey('courses.id'))
              )

class User(Base):
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(200), nullable=False)

class Course(Base, Jsonify):
    id = Column(Integer, primary_key=True, autoincrement=True)
    members = relationship('User', secondary=course_members, backref=backref('courses', lazy='dynamic'))
    roles = relationship('Role', secondary=course_roles, backref=backref('roles', lazy='dynamic'))

class Role(Base):

    id = Column(Integer, primary_key=True, autoincrement=True)
    role_id = Column(Integer, nullable=False)
    user_id = Column(Integer, ForeignKey('users.id'))
    user = relationship('User', backref=backref("roles", lazy='dynamic'))
    
    UniqueConstraint('role_id', 'user_id', name='role_combination')

With a user query:

print(type(user.roles))
print(user.roles.filter_by(id=1).first())
# print(user.courses.first() <-- Works fine too.

<class 'sqlalchemy.orm.dynamic.AppenderQuery'>
{id: 1, 'user_id': 1, role_id: 3}

But with a course query:

print(type(course.roles))
print(course.roles.filter_by(id=1).first())

<class 'sqlalchemy.orm.collections.InstrumentedList'>
AttributeError: 'InstrumentedList' object has no attribute 'filter_by'

Same result when I try with members.

Using the members and courses as list objects ofc work:

course.roles[0].user.first()

But I really miss the functions of the AppenderQuery class for my course query.

Is this normal behavior or am I missing something?

Answer 1

While writing the question and researching I found the answer to the problem in this post add dynamic to the other side.

And after seeing the answer I understood more of the relationship functionality as well.

members = relationship('User', secondary=course_members, lazy='dynamic', backref=backref('courses', lazy='dynamic'))
roles = relationship('Role', secondary=course_roles, lazy='dynamic', backref=backref('roles', lazy='dynamic'))

The members and roles relationships are ofc the functionality of the parent class, and the backref=* is ofc the relationship functionality of the child class. It took me more time than I liked to realize this. But by having lazy='dynamic' as a parameter in both the relationship function, and the backref function for the backref parameter effectively applies this to both sides of the relationship.

And now users, courses and roles are returned as AppenderQuery.

Hopefully this will help others in search of the question.