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 [email protected].
To post to this group, send email to [email protected].
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.
