I've tested the patch. Works perfectly with my usecase. Thank you very much !
Le vendredi 19 juillet 2019 20:22:40 UTC+3, Mike Bayer a écrit : > > > > 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/. > <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 sqlal...@googlegroups.com <javascript:>. > To post to this group, send email to sqlal...@googlegroups.com > <javascript:>. > 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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/ed876ade-52c7-46cd-b723-592358f5cc77%40googlegroups.com.
