Yeah, new api is great, eg: loader_fn = self.lazy_loader_map.get(relation_attribute.prop.lazy, 'selectinload') self.opts.append(loader_fn(relation_attribute.and_(*relation_filter).options(*related.opts)) or with_loader_criteria and events you mentioned.
Thanks again. Have a nice weekend. piątek, 10 września 2021 o 15:14:59 UTC+2 Mike Bayer napisał(a): > 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+...@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/ebf1dcaa-c4e4-485b-b95b-8b44c4559899n%40googlegroups.com.