CODEWITHSUNDEEP
pythonormsqlalchemy
Saqib Ali
Saqib Ali

Reputation: 12635

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??

Upvotes: 0

Views: 250

Answers (1)

van
van

Reputation: 77082

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.

Upvotes: 2

Related Questions