Dear sqlalchemy users, After look at the docs I arrived at the following structure:
``` Base = declarative_base() parent_child = Table( "parent_child", Base.metadata, Column("parent_id", Integer, ForeignKey("parent.id")), Column("child_id", Integer, ForeignKey("child.id")), ) class Parent(Base): __tablename__ = "parent" id = Column(Integer, primary_key=True) children = relationship("Child", secondary=parent_child, backref="children") class Child(Base): __tablename__ = "child" id = Column(Integer, primary_key=True) ``` When I run the following query with a parent that has 300.000 children it takes approximately 25 seconds to load the children. ``` parent = session.query(Parent).filter_by(id=96).first() children = parent.children ``` Now the query it ends up doing when collecting the children is: ``` app_1 | INFO:sqlalchemy.engine.base.Engine:{'id_1': 96, 'param_1': 1} app_1 | INFO:sqlalchemy.engine.base.Engine:SELECT child.id AS child_id app_1 | FROM child, child_parent app_1 | WHERE %(param_1)s = child_parent.parent_id AND child.id = child_parent.child_id app_1 | INFO:sqlalchemy.engine.base.Engine:{'param_1': 96} ``` It feels like this should be a join and not a cartesian product with a where statement. What am I doing wrong? Or how can I make sure that it does a join when fetching the children instead of this. Cheers -- 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/6c757c2d-f59b-41d6-950a-3b8e2f7e1731n%40googlegroups.com.