Hello Mike,
Thank you very much for your response.
I ended up with that, but i wanna ask you about this solution.
class A(Base):
id: int = Column('a_id', Integer, primary_key=True, autoincrement=True)
#descr
class B(Base):
id: int = Column('b_id', Integer, primary_key=True, autoincrement=True)
a_id: int = Column('a_id', Integer, ForeignKey('a.a_id'))
c_relationship = relationship("C", secondary='b_rel_c', lazy='joined', uselist=False)
#descr_cat
class C(Base):
id: int = Column('c_id', Integer, primary_key=True, autoincrement=True)
c_attr1: int = Column(Integer, nullable=False)
c_global_attr_1: int = Column(Integer, nullable=False)
class B_rel_C(Base):
b_id: int = Column('a_id', Integer, ForeignKey('a.a_id'))
c_id: int = Column('a_id', Integer, ForeignKey('c.c_id'))
some_kw: int = Column(Integer)
class CustomSelectClass(Base):
__table__ = select(B, C.c_global_attr_1, C.c_attr1).join(B_rel_C, B_rel_C.b_id == B.id). \
join(C, C.id == B_rel_C.c_id).where(
B.a_id == A.id,
C.c_global_attr_1 == bindparam('c_global_attr_1')). \
limit(100).subquery().lateral()
A.b_filtered_rel = relationship(CustomSelectClass, lazy='joined')
class X(Base):
id: int = Column('x_id', Integer, primary_key=True, autoincrement=True)
a_id: int = Column('a_id', Integer, ForeignKey('a.a_id'))
I use this aproach also with another schema like this: X -> A -> A_REL_B <- B -> C
s = select(A).where(A.id == 1)
s = s.options(joinedload(A.b_filtered_rel))
or
s = select(X).options(joinedload(A).options(joinedload(B)))
result = session.execute(s, dict(c_global_attr_1=1))
s = s.options(selectionload(A.b_filtered_rel))
or
s = select(X).options(selectionload(A).options(joinedload(B)))
or
s = select(X).options(joinedload(A).options(selectionload(B)))
result = session.execute(s, dict(c_global_attr_1=1))
What do you think about ability to pass some arguments to query emitted with selectinload