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 ? -- 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. For more options, visit https://groups.google.com/d/optout.