great, glad you were able to work that out that's a lot of new API to learn
On Fri, Sep 10, 2021, at 3:39 AM, Tomas Pavlovsky wrote: > Thank you very much, > It helped me a lot, UserDefinedOption and do_orm_execute, I missed it in the > documentation > > P.S. Thank you for sqlalchemy, it's really helpful and thanks to nested > options it's really fun to use. > wtorek, 31 sierpnia 2021 o 16:41:07 UTC+2 Mike Bayer napisał(a): >> __ >> >> 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+...@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/7ecac2f9-0d17-4f44-92b8-6127f61bf740n%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/7ecac2f9-0d17-4f44-92b8-6127f61bf740n%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/87f791fc-e872-4a86-8a0d-ca07c47530d1%40www.fastmail.com.
