I have 3 models - A (root), B (child), C (grandchild)
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()
class A(db.Model):
__tablename__ = 'A'
a_id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(255), nullable=False)
# Specifying relationship to B
b_id = db.Column(db.ForeignKey('B.b_id', ondelete='CASCADE'), index=True)
b = db.relationship('B', primaryjoin='A.b_id == B.b_id',
lazy="joined", innerjoin=True, join_depth=1)
class B(db.Model):
__tablename__ = 'B'
b_id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(255), nullable=False)
# Specifying relationship to C
c_id = db.Column(db.ForeignKey('C.c_id', ondelete='CASCADE'), index=True)
c = db.relationship('C', primaryjoin='B.c_id == C.c_id',
lazy="joined", innerjoin=True, join_depth=1)
class C(db.Model):
__tablename__ = 'C'
c_id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(255), nullable=False)
The intention is to retrieve only A and B when I query (using all()) for A. I have used the lazy=joined and join_depth=1 options.
This indeed breaks the query into multiple parts so that each of them aren't having more than one join. But it fires multiple queries and joins C as well.
So two questions:
- How do I ensure I retrieve only A and B even though C is related?
- In the general case, can I ensure that only models upto a certain depth are fetched?
join_depth seems to be carrying that sense, but it would be great if it stopped with just one query.