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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/6c757c2d-f59b-41d6-950a-3b8e2f7e1731n%40googlegroups.com.
