Selectinload and bindparam

31 views
Skip to first unread message

Tomas Pavlovsky

unread,
Aug 30, 2021, 10:55:52 AMAug 30
to sqlalchemy
Hello, 

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

Thanks,
Tomas

Mike Bayer

unread,
Aug 30, 2021, 11:50:49 AMAug 30
to noreply-spamdigest via sqlalchemy
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
 
 
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.

Tomas Pavlovsky

unread,
Aug 31, 2021, 4:34:55 AMAug 31
to sqlalchemy
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

unread,
Aug 31, 2021, 4:46:50 AMAug 31
to sqlalchemy
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

unread,
Aug 31, 2021, 10:41:07 AMAug 31
to noreply-spamdigest via sqlalchemy

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

Tomas Pavlovsky

unread,
Sep 10, 2021, 3:39:06 AMSep 10
to sqlalchemy
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

unread,
Sep 10, 2021, 9:14:59 AMSep 10
to noreply-spamdigest via sqlalchemy
great, glad you were able to work that out that's a lot of new API to learn

Tomas Pavlovsky

unread,
Sep 10, 2021, 11:48:37 AMSep 10
to sqlalchemy
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.
Reply all
Reply to author
Forward
0 new messages