CODEWITHSUNDEEP
pythonflasksqlalchemyflask-sqlalchemy
stefanobaldo
stefanobaldo

Reputation: 2063

Make a relationship between two tables though another

class Condominium(db.Model):
    __tablename__ = 'condominiums'
    id = db.Column(db.Integer, primary_key=True)
    properties = db.relationship('Property', backref='condominiums')

class Property(db.Model):
    __tablename__ = 'properties'
    id = db.Column(db.Integer, primary_key=True)
    condominium_id = db.Column(db.Integer, db.ForeignKey('condominiums.id'))
    listings = db.relationship('Listing', backref='property')

class Listing(db.Model):
    __tablename__ = 'listings'
    id = db.Column(db.Integer, primary_key=True)
    property_id = db.Column(db.Integer, db.ForeignKey('properties.id'))

I want to list all listings for a given condominium, like this:

SELECT listings.* FROM condominiums
INNER JOIN properties ON properties.condominium_id = condominiums.id
INNER JOIN listings ON listings.property_id = properties.id
WHERE condominiums.id = 1;

I want to be able to get a listing collection like this:

condominium = Condominium.query.get(1)
listings = condominium.listings

How can I achieve that using SQLAlchemy? Is it possible?

Upvotes: 0

Views: 86

Answers (1)

dylrei
dylrei

Reputation: 1738

Assuming Condominium is 1:M to Property which is 1:M to Listing and that all foreign keys are well-defined, you can get all Listings for Condominium with id 123 thus:

session.query(Listing).join(Property).join(Condominium).filter(Condominium.id=123)

Upvotes: 1

Related Questions