SQLAlchemy many-to-many association querying specific child

user_5094261 0
Viewed 98

In the case of many-to-many relationships, an association table can be used in the form of Association Object pattern.

I have the following setup of two classes having a M2M relationship through UserCouncil association table.

class Users(Base):
    name = Column(String, nullable=False)
    email = Column(String, nullable=False, unique=True)
    created_at = Column(DateTime, default=datetime.utcnow)
    password = Column(String, nullable=False)
    salt = Column(String, nullable=False)
    
    councils = relationship('UserCouncil', back_populates='user')


class Councils(Base):
    name = Column(String, nullable=False)
    created_at = Column(DateTime, default=datetime.utcnow)

    users = relationship('UserCouncil', back_populates='council')


class UserCouncil(Base):
    user_id = Column(UUIDType, ForeignKey(Users.id, ondelete='CASCADE'), primary_key=True)
    council_id = Column(UUIDType, ForeignKey(Councils.id, ondelete='CASCADE'), primary_key=True)

    role = Column(Integer, nullable=False)

    user = relationship('Users', back_populates='councils')
    council = relationship('Councils', back_populates='users')

However, in this situation, suppose I want to search for a council with a specific name cname for a given user user1. I can do the following:

for council in user1.councils:
    if council.name == cname:
        dosomething(council)

Or, alternatively, this:

session.query(UserCouncil) \
       .join(Councils)     \
       .filter((UserCouncil.user_id == user1.id) & (Councils.name == cname)) \
       .first()            \
       .council

While the second one is more similar to raw SQL queries and performs better, the first one is simpler. Is there any other, more idiomatic way of expressing this query which is better performing while also utilizing the relationship linkages instead of explicitly writing traditional joins?

many-to-many sqlalchemy associations orm python
1 Answers

First, I think even the SQL query you bring as an example might need to go to fetch the UserCouncil.council relationship again to the DB if it is not loaded in the memory already.

I think that given you want to search directly for the Council instance given its .name and the User instance, this is exactly what you should ask for. Below is the query for that with 2 options on how to filter on user_id (you might be more familiar with the second option, so please use it):

q = (
    select(Councils)
    .filter(Councils.name == councils_name)
    .filter(Councils.users.any(UserCouncil.user_id == user_id))  # v1: this does not require JOIN, but produces the same result as below
    # .join(UserCouncil).filter(UserCouncil.user_id == user_id)    # v2: join, very similar to original SQL
)
council = session.execute(q).scalars().first()

As to making it more simple and idiomatic, I can only suggest to wrap it in a method or property on the User instance:

class Users(...):
    ...
    def get_council_by_name(self, councils_name):
        q = (
            select(Councils)
            .filter(Councils.name == councils_name)
            .join(UserCouncil).filter(with_parent(self, Users.councils))
        )
        return object_session(self).execute(q).scalars().first()

so that you can later call it user.get_council_by_name('xxx')

Edit-1: added SQL queries

v1 of the first q query above will generate following SQL:

SELECT  councils.id,
        councils.name
FROM    councils
WHERE   councils.name = :name_1
  AND  (EXISTS
         (SELECT  1
          FROM    user_councils
          WHERE   councils.id = user_councils.council_id
            AND   user_councils.user_id = :user_id_1
         )
       )

while v2 option will generate:

SELECT  councils.id,
        councils.name
FROM    councils
JOIN    user_councils ON councils.id = user_councils.council_id
WHERE   councils.name = :name_1
  AND   user_councils.user_id = :user_id_1
user_995940
Related