How to join data from two tables in SQLAlchemy?

Question

I have 3 tables: Account, User and Organization.

• Account consists of id, name and organization_id.
• User consists of email and organization_id.
• Organization consists of id and name.

Each Account is registered to an Organization(through organization_id) and each User is registered to an Organization. The challenge is to display all the emails (from User) to the Account corresponding to the name whose organization_id matches the organization_id of User.

Here's my code till now:

class Account(db.Model):
    __tablename__ = "account"
    id = Column(Integer, primary_key=True)
    name = Column(String(50), index=True, unique=True)
    organization = Column(Integer, 
        ForeignKey("organization.id"),nullable=False, index=True)

class User(UserMixin, db.Model, RBACUserMixin):
    __tablename__ = "user"
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(255), unique=True)
    organization = Column(Integer, ForeignKey("organization.id"), 
                      nullable=False, index=True)

class Organization(db.Model):
    __tablename__ = "organization"
    id = Column(Integer, primary_key=True)
    name = Column(String(512))
    users = relationship("User", backref="organizations")
    accounts = relationship("Account", backref="organizations")

Answer 1

Join user and account on organization id and filter based on name:

db.session.query(User.email).\
    join(Account, Account.organization == User.organization).\
    filter(Account.name == 'some name')

Query.join() allows passing arbitrary SQL expressions as the on-clause when using the 2 argument form.