Selectinload and bindparam

[Tomas Pavlovsky]

Hello, 

is it possible in sqlalchemy arguments from db.execute(query, args) to be passed to query emitted by selectinload?

Thanks,

Tomas

[Mike Bayer]

yes, you would use bindparam("some_name") in conjunction with the "and_()" feature, like  options(selectinload(MyClass.foo.and_(MyClass.name == bindparam("some_name"))))

--

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.

[Tomas Pavlovsky]

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

[Tomas Pavlovsky]

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?

[Mike Bayer]

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

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/493a25e1-6e76-4ab3-a1ce-94f3ec8f206bn%40googlegroups.com.

[Tomas Pavlovsky]

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.

[Mike Bayer]

great, glad you were able to work that out that's a lot of new API to learn

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/7ecac2f9-0d17-4f44-92b8-6127f61bf740n%40googlegroups.com.

[Tomas Pavlovsky]

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.