Saqib Ali Saqib Ali - 27 days ago 9
Python Question

What SQLAlchemy Query should I run to traverse this backreference?

I have the following 3 SQLAlchemy Models:

class MyClassA(db.Model):
myclassa_id = db.Column(db.Integer, nullable=False, primary_key=True)
myfield_1 = db.Column(db.String(1024), nullable=True)
myclassbs = db.relationship("MyClassB", backref="myclassa")

class MyClassC(db.Model):
myclassc_id = db.Column(db.Integer, nullable=False, primary_key=True)
myfield_2 = db.Column(db.String(1024), nullable=False, unique=True)

class MyClassB(db.Model):
legal_document_content_format_id = db.Column(db.Integer, nullable=False, primary_key=True)
myclassa_id = db.Column(db.Integer, db.ForeignKey(MyClassA.myclassa_id), nullable=False)
myclassc_id = db.Column(db.Integer, db.ForeignKey(MyClassC.myclassc_id))
myfield_3 = db.Column(db.String(1024))
__table_args__ = (db.UniqueConstraint('myclassa_id', 'myclassc_id', name='unique_constraint_mcaid_mccid'),)


Here is what I want to do:

Suppose I have
a
-- an instance of
MyClassA
.

I want to return
b
-- the instance of
MyClassB
-- that meets the following 2 criteria:


  1. b
    is a child record of
    a

  2. b
    is a child record of the instance of MyClassC which has
    my_field2 == "Hello"



If no record meets those criteria, throw an exception.
How can I do this in a few compact lines of code??

van van
Answer
q = (
    db.session.query(MyClassB)
    .join(MyClassA)  # this will satisfy first criteria
    .join(MyClassC)  # criteria 2 part 1
    .filter(MyClassC.my_field2 == "Hello")  # criteria 2 part2
)

b_list = q.all()
assert b_list

But for this to work you also need to add relationship between B and C:

class MyClassC(db.Model):
    # ...
    myclassbs = db.relationship("MyClassB", backref="myclassc")

If you do not want to do that, you can modify the query not to rely on the relationship definition.