How to define table relationships in SQLAlchemy with multi-level / multiple joins?

Question

I am trying to determine the relationship between two tables whose relationships are indirect (i.e. through two other tables).

The results I'm looking for can be obtained using this query:

(db.session.query(Telnum) .filter(Account.customer==customer) .filter(Account.account_id == Subscription.account_id) .filter(Telnum.sub_id == Subscription.id) .order_by(Telnum.telnum) .all() ) 

where customer is the Customer object.

I am struggling to understand how this will be defined as a relationship similar to Customer.invoices . My idea was something like this:

 telnums = db.relationship('Telnum', primaryjoin="and_(Account.user_id==Customer.id, " "Account.account_id == Subscription.account_id, " "Telnum.sub_id == Subscription.id)", backref='customer') 

As can be seen from this publication, this does not work. The error message it produces is the following: sqlalchemy.exc.ArgumentError: Could not locate any simple equality expressions involving locally mapped foreign key columns for primary join condition 'accounts.user_id = customers.id AND accounts.account_id = subscriptions.account_id AND pstn_numbers.sub_id = subscriptions.id' on relationship Customer.telnums. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or are annotated in the join condition with the foreign() annotation. To allow comparison operators other than '==', the relationship can be marked as viewonly=True. sqlalchemy.exc.ArgumentError: Could not locate any simple equality expressions involving locally mapped foreign key columns for primary join condition 'accounts.user_id = customers.id AND accounts.account_id = subscriptions.account_id AND pstn_numbers.sub_id = subscriptions.id' on relationship Customer.telnums. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or are annotated in the join condition with the foreign() annotation. To allow comparison operators other than '==', the relationship can be marked as viewonly=True.

Can someone hint me in the right direction?

I have the following table structure (simplified, all non-matching columns are removed separately from one for each table):

 class Customer(db.Model): __tablename__ = 'customers' id = db.Column(db.Integer, primary_key=True) identification_num = db.Column(db.String(10), unique=True) name = db.Column(db.Text) invoices = db.relationship('Invoice', backref='customer') accounts = db.relationship('Account', backref='customer') def __init__(self): pass def __repr__(self): return '<Customer %r>' % (self.name) class Invoice(db.Model): __tablename__ = 'invoices' id = db.Column(db.Integer, primary_key=True) customer_id = db.Column(db.Integer, db.ForeignKey('customers.id')) active = db.Column(db.Boolean) accounts = db.relationship('Account', backref='invoice') def __repr__(self): return '<Invoice %r>' % (self.id) class Account(db.Model): __tablename__ = 'accounts' id = db.Column(db.Integer, primary_key=True) account_id = db.Column(db.Integer, unique=True) invoice_id = db.Column(db.Integer, db.ForeignKey('invoices.id')) user_id = db.Column(db.Integer, db.ForeignKey('customers.id')) active = db.Column(db.Boolean) subscriptions = db.relationship('Subscription', backref='account') def __repr__(self): return '<Account %r>' % (self.account_id) class Subscription(db.Model): __tablename__ = 'subscriptions' id = db.Column(db.Integer, primary_key=True) account_id = db.Column(db.Integer, db.ForeignKey('accounts.account_id')) sub_active = db.Column(db.DateTime) telnums = db.relationship('Telnum', backref='subscription') def __repr__(self): return '<Subscription %r>' % (self.id) class Telnum(db.Model): __tablename__ = 'pstn_numbers' id = db.Column(db.Integer, primary_key=True) sub_id = db.Column(db.Integer, db.ForeignKey('subscriptions.id')) telnum = db.Column(db.String(64)) holder = db.Column(db.String(10)) def __repr__(self): return '<Telnum %r>' % (self.telnum) 

Answer 1

In general, I would not define an indirect relationship as a relationship , because you risk that these indirect relationships become unsynchronized when you make changes. You can work around some of these limitations by specifying the viewonly=False parameter for relationship .

A simpler, less risky and more straightforward solution would be to use a query (or a property with query support) if you would like to reload data from the database and use the python list methods to get the subtitle, children of the relations tree:

 class Customer(Base): # ... @property def telnums_qry(self): sess = Session.object_session(self) return (sess.query(Telnum) .join(Subscription) .join(Account) .filter(Account.user_id == self.id) ).all() @property def telnums_mem(self): return [tel for acc in self.accounts for sub in acc.subscriptions for tel in sub.telnums ] class Telnum(Base): # ... @property def customer(self): return (self.subscription and self.subscription.account and self.subscription.account.customer )