CODEWITHSUNDEEP
pythonsqlalchemy
Akash
Akash

Reputation: 349

SQLAlchemy Relationships between 3 tables

I am using Python 2.7.2 and SQLAlchemy 0.9.3.

A Department has many groups. A Group has many members. A Member can only be in one group.

Currently to get a list of all the members of a Department. I first get all the groups and then get the members of those groups.

Is there a way to go directly from a department to members without going via groups table ?

class Department(Base):
    __tablename__ = 'departments'

    id = Column(Integer, primary_key=True)
    name = Column(String(64), unique=True)
    location = Column(String(32), unique=True)
    groups = relationship("Group", backref="department")

class Group(Base):
    __tablename__ = 'groups'

    id = Column(Integer, primary_key=True)
    name = Column(String(64), unique=True)
    department_id = Column(Integer, ForeignKey('departments.id'))
    members = relationship("Member", backref="group")

class Member(Base):
    __tablename__ = 'members'

    id = Column(Integer, primary_key=True)
    group_id = Column(Integer, ForeignKey('groups.id'))

Upvotes: 2

Views: 1612

Answers (1)

James Bingo
James Bingo

Reputation: 286

This kind of query requires a join statement:

q = session.query(Member).\
        join(Member.group).\
        join(Group.department).\
        filter(Department.name=='depart_name')

result = q.all()

This will return a list of Member Objects where the member is part of the department depart_name

Upvotes: 2

Related Questions