SQLAlchemy - Limit the number of relationships queried by depth

Viewed 289

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:

  1. How do I ensure I retrieve only A and B even though C is related?
  2. 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.

0 Answers
Related