On Fri, Jul 19, 2019, at 4:40 AM, yoch melka wrote: > Thank you for your help. > > I'd be happy to submit a PR (I've already tried to fix this by myself), but > the code is a little hard to master.
I'm glad you tried. The feature is in review at https://gerrit.sqlalchemy.org/#/c/1368/. Of particular importance is that it has a new "degrade" step in the very unlikely case that the foreign key attributes on the parent object are unloaded; rather than emitting a separate query for those, it goes back to the JOIN. I would encourage you, if possible, to download the patch for the 1.3 series from https://gerrit.sqlalchemy.org/#/c/sqlalchemy/sqlalchemy/+/1369/ and ensure it works and performs as expected for your use case, though a SQLAlchemy release is now overdue and this may go out quickly. > > Le vendredi 19 juillet 2019 01:24:03 UTC+3, Mike Bayer a écrit : >> >> >> On Thu, Jul 18, 2019, at 5:02 PM, yoch melka wrote: >>> Hi, >>> >>> According to the documentation >>> <https://docs.sqlalchemy.org/en/13/orm/loading_relationships.html#select-in-loading>, >>> from 1.3 "selectin loading can omit the JOIN for a simple one-to-many >>> collection" in case if the PK is known by the primary request. >>> >>> But in the opposite case that we know the FK, it's not exploited by >>> Sqlalchemy to produce more effective queries. >>> >>> Example: >>> >>> from sqlalchemy import Table, Column, Integer, ForeignKey, create_engine >>> from sqlalchemy.orm import relationship, sessionmaker, selectinload >>> from sqlalchemy.ext.declarative import declarative_base >>> >>> >>> Base = declarative_base() >>> >>> class Parent(Base): >>> __tablename__ = 'parent' >>> id = Column(Integer, primary_key=True) >>> child_id = Column(Integer, ForeignKey('child.id')) >>> child = relationship("Child") >>> >>> class Child(Base): >>> __tablename__ = 'child' >>> id = Column(Integer, primary_key=True) >>> >>> >>> engine = create_engine('sqlite://') >>> engine.echo = True >>> >>> Base.metadata.create_all(engine) >>> >>> Session = sessionmaker(bind=engine) >>> db=Session() >>> >>> # create some data >>> c1 = Child() >>> db.add(c1) >>> for n in range(10): >>> p = Parent(child=c1) >>> db.add(p) >>> db.commit() >>> >>> >>> l = db.query(Parent).options(selectinload(Parent.child)).all() >>> >>> The last instruction above will produce the following two SQL queries : >>> >>> SELECT parent.id AS parent_id, parent.child_id AS parent_child_id >>> FROM parent >>> >>> SELECT parent_1.id AS parent_1_id, child.id AS child_id >>> FROM parent AS parent_1 JOIN child ON child.id = parent_1.child_id >>> WHERE parent_1.id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10) >>> ORDER BY parent_1.id >>> >>> But in fact, the ORM have enough information to made the second query >>> simply: >>> >>> SELECT child.id AS child_id >>> FROM child >>> WHERE id IN (1) >>> >>> because we know that all child_id we have in parents are 1. >>> >>> Is it can be improved ? >> >> >> certainly, do you have resources to work on a pull request? it would require >> a new method like _init_for_omit_join() [1] as well as an alternate strategy >> inside of _load_for_path() [2] which loads the related objects, and matches >> them to the parents, in a different way. >> >> You can get almost the same effect right now, with a little bit less >> internal effiency, to just load the Child objects directly in a list, then >> access the attribute upon each Parent, which loads them from the identity >> map: >> >> l = db.query(Parent).all() >> c = db.query(Child).filter(Child.id.in_([p.id for p in l])).all() >> for p in l: >> p.child # no query is emitted >> >> >> a PR for this feature could be informed by a similar strategy, though the >> "p.child" part should likely use a faster route to populating the attribute >> directly the way the current strategy does. >> >> >> [1] >> https://github.com/sqlalchemy/sqlalchemy/blob/master/lib/sqlalchemy/orm/strategies.py#L2097 >> [2] >> https://github.com/sqlalchemy/sqlalchemy/blob/master/lib/sqlalchemy/orm/strategies.py#L2196 >> >> >>> >>> >>> -- >>> 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 sqlal...@googlegroups.com. >>> To post to this group, send email to sqlal...@googlegroups.com. >>> Visit this group at https://groups.google.com/group/sqlalchemy. >>> To view this discussion on the web visit >>> https://groups.google.com/d/msgid/sqlalchemy/b199fba3-8aaf-4e23-b406-865c3e57986c%40googlegroups.com >>> >>> <https://groups.google.com/d/msgid/sqlalchemy/b199fba3-8aaf-4e23-b406-865c3e57986c%40googlegroups.com?utm_medium=email&utm_source=footer>. >>> For more options, visit https://groups.google.com/d/optout. >> > > -- > 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 post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at https://groups.google.com/group/sqlalchemy. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/efc5ccd1-e94a-4b13-909e-62063325f332%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/efc5ccd1-e94a-4b13-909e-62063325f332%40googlegroups.com?utm_medium=email&utm_source=footer>. > For more options, visit https://groups.google.com/d/optout. -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/d864abc8-1b40-4b83-b479-e6eec626b8b3%40www.fastmail.com. For more options, visit https://groups.google.com/d/optout.
