CODEWITHSUNDEEP
pythonsqlsqlalchemysubquery
bpedman
bpedman

Reputation: 193

Select through multiple many to many relationships via SQLAlchemy

I want to be able to query through multiple many to many relationships in SQLAlchemy.

I have Users, which are associated to Groups, which are associated to Roles. All associations are many-to-many. I want to get the list of Roles that are associated with the User through the Groups.

Here are my striped down models:

class User(Model):
    id = Column(Integer)
    groups = relationship('Group', secondary=user_group)

class Group(Model):
    id = Column(Integer)
    roles = relationship('Role', secondary=role_group)

class Role(Model):
    id = Column(Integer)

I have a rough idea of what SQL would be used:

select distinct role.* from role, role_group
where role.id = role_group.role_id
  and role_group.group_id in 
    (select "group".id from "group", user_group 
     WHERE user_group.user_id = 1 
       and "group".id = user_group."group.id")

But I am having a hard time figuring out how to translate that into SQLAlchemy...and I am not sure even if the SQL here is the best way to do this or not.

Upvotes: 2

Views: 1419

Answers (2)

zaquest
zaquest

Reputation: 2050

Try this

s = Session()
s.query(Role).join(User.groups).join(Group.roles).filter(User.id==1).all()

it will generate following sql query

SELECT role.id AS role_id 
FROM user
JOIN user_group AS user_group_1 ON user.id = user_group_1.user_id
JOIN "group" ON "group".id = user_group_1.group_id
JOIN role_group AS role_group_1 ON "group".id = role_group_1.group_id
JOIN role ON role.id = role_group_1.role_id 
WHERE user.id = 1;

however sqlalchemy will only return distinct roles (if you will query Role.id then sqlalchemy will return what sql query actually returns).

Upvotes: 3

bpedman
bpedman

Reputation: 193

I did find a solution to my own question that would generate close to the SQL I was looking for. However, I believe zaquest's answer is much better and will be using that instead. This is just for posterity's sake.

Here is the query I came up with:

s = Session()
s.query(Role).join(Role.groups).filter(
        Group.id.in_(s.query(Group.id).join(User.groups)
                .filter(User.id == self.id)))

This would generate the following SQL

SELECT role.id AS role_id
FROM role JOIN role_group AS role_group_1 ON role.id = role_group_1.role_id 
JOIN "group" ON "group".id = role_group_1.group_id 
WHERE "group".id IN 
  (SELECT "group".id AS group_id 
   FROM "user" JOIN user_group AS user_group_1 ON "user".id = user_group_1.user_id 
   JOIN "group" ON "group".id = user_group_1."group.id" 
   WHERE "user".id = :id_1)

This is similar to the SQL I was looking for. The key that I forgot about was that I can use another query as part of the in operator in SQLAlchemy.

Upvotes: 0

Related Questions