this is true, the parameters can't be known to be passed along to a secondary query. the approach here would require that you make careful use of custom user options and events in order to make it work. the general event hook you would use is documented at https://docs.sqlalchemy.org/en/14/orm/session_events.html#adding-global-where-on-criteria . I dont have the resouces to work up a complete example however an example of creating custom user options is in the dogpile.cache example at https://docs.sqlalchemy.org/en/14/orm/examples.html#examples-caching . the user option object you would use is based on : https://github.com/sqlalchemy/sqlalchemy/blob/master/lib/sqlalchemy/orm/interfaces.py#L792
basically build a UserDefinedOption class that carries along the parameters you want. then in do_orm_execute() apply those parameters to the query when the option is present. you can consume user defined options using the user_defined_options collection: https://docs.sqlalchemy.org/en/14/orm/session_api.html?highlight=ormexecutestate#sqlalchemy.orm.ORMExecuteState.user_defined_options On Tue, Aug 31, 2021, at 4:46 AM, Tomas Pavlovsky wrote: > Sorry, once again :) > > 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')) > a = relationship(A) > > s = select(A).where(A.id == 1) > s = s.options(joinedload(A.b_filtered_rel)) > or > s = select(X).options(joinedload(X.a).options(joinedload(A.b_filtered_rel))) > result = session.execute(s, dict(c_global_attr_1=1)) > > s = s.options(*selectionload*(A.b_filtered_rel)) > or > s = > select(X).options(*selectionload*(X.a).options(joinedload(A.b_filtered_rel))) > or > s = > select(X).options(joinedload(X.a).options(*selectionload*(A.b_filtered_rel))) > result = session.execute(s, dict(c_global_attr_1=1)) > This query will fail, beacuse only params passed to query are related Pks. > What do you think about ability to pass some arguments to query emitted with > selectinload? > > wtorek, 31 sierpnia 2021 o 10:34:55 UTC+2 Tomas Pavlovsky napisał(a): >> 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 >> >> poniedziałek, 30 sierpnia 2021 o 17:50:49 UTC+2 Mike Bayer napisał(a): >>> __ >>> yes, you would use bindparam("some_name") in conjunction with the "and_()" >>> feature, like options(selectinload(MyClass.foo.and_(MyClass.name == >>> bindparam("some_name")))) >>> >>> >>> >>> On Mon, Aug 30, 2021, at 10:55 AM, Tomas Pavlovsky wrote: >>>> Hello, >>>> >>>> is it possible in sqlalchemy arguments from db.execute(query, args) to be >>>> passed to query emitted by selectinload? >>>> >>>> Thanks, >>>> Tomas >>>> >>>> >>>> -- >>>> SQLAlchemy - >>>> The Python SQL Toolkit and Object Relational Mapper >>>> >>>> http://www.sqlalchemy.org/ >>>> >>>> To post example code, please provide an MCVE: Minimal, Complete, and >>>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full >>>> description. >>>> --- >>>> You received this message because you are subscribed to the Google Groups >>>> "sqlalchemy" group. >>>> To unsubscribe from this group and stop receiving emails from it, send an >>>> email to sqlalchemy+...@googlegroups.com. >>>> To view this discussion on the web visit >>>> https://groups.google.com/d/msgid/sqlalchemy/7cbbfb10-1c4f-4369-a30d-e83cee262d16n%40googlegroups.com >>>> >>>> <https://groups.google.com/d/msgid/sqlalchemy/7cbbfb10-1c4f-4369-a30d-e83cee262d16n%40googlegroups.com?utm_medium=email&utm_source=footer>. >>> > > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/493a25e1-6e76-4ab3-a1ce-94f3ec8f206bn%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/493a25e1-6e76-4ab3-a1ce-94f3ec8f206bn%40googlegroups.com?utm_medium=email&utm_source=footer>. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/4c39d666-40fa-40c1-9e79-29c8356d82bb%40www.fastmail.com.
