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 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/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/d6cb68a9-b3e3-475d-aa67-895a1cdd83ab%40www.fastmail.com. For more options, visit https://groups.google.com/d/optout.
