SQLAlchemy: фильтр по членству по крайней мере в одной таблице, связанной со многими ко многим.

Используя SQLAlchemy 0.7.1 и базу данных MySQL 5.1, у меня установлено отношение "многие ко многим":

user_groups = Table('user_groups', Base.metadata,
    Column('user_id', String(128), ForeignKey('users.username')),
    Column('group_id', Integer, ForeignKey('groups.id'))
)

class ZKUser(Base, ZKTableAudit):
    __tablename__ = 'users'

    username   = Column(String(128), primary_key=True)
    first_name = Column(String(512))
    last_name  = Column(String(512))

    groups = relationship(ZKGroup, secondary=user_groups, backref='users')

class ZKGroup(Base, ZKTableAudit):
    __tablename__ = 'groups'

    id          = Column(Integer, primary_key=True)
    name        = Column(String(512))

Пользователи могут принадлежать нескольким группам, а группы могут содержать несколько пользователей.

То, что я пытаюсь сделать, это построить запрос SQLAlchemy, который возвращает только пользователей, которые принадлежат хотя бы одной группе из списка групп.

Я играл с функцией in_, но это, похоже, работает только для тестирования скалярных значений для членства в списке. Я не очень писатель SQL, поэтому я даже не знаю, что это за SELECT, что это потребует.

Ответ 1

Хорошо, после большого исследования я понял, что это мое собственное незнание терминологии SQL, которая удерживала меня. Мой поиск решения для поиска пользователей, принадлежащих к "хотя бы одному из" списка групп, должен был найти пользователей, принадлежащих к "любому" из списка групп. Функция any ORM от SQLAlchemy делает именно то, что мне нужно, например:

session.query(ZKUser).filter(ZKUser.groups.any(ZKGroup.id.in_([1,2,3])))

Этот код испускает этот SQL (на MySQL 5.1):

SELECT * FROM users 
WHERE EXISTS (
    SELECT 1 FROM user_groups, groups 
    WHERE users.id = user_groups.contact_id 
        AND groups.id = user_groups.group_id 
        AND groups.id IN (%s, %s, %s)
    )

Ответ 2

В соответствии с docs для any запрос будет работать быстрее, если вместо этого использовать явный join:

Поскольку any() использует коррелированный подзапрос, его производительность не так хороша при сравнении с большими целевыми таблицами, как с использованием соединения.

В вашем случае вы можете сделать что-то вроде:

users = (
    session.query(ZKUser)
    .join(user_groups)
    .filter(user_groups.columns.group_id.in_([1, 2, 3]))
)

Это испускает SQL как:

SELECT *
FROM users
JOIN user_groups ON users.id = user_groups.user_id 
WHERE user_groups.group_id IN (1, 2, 3)

Ответ 3

Вы можете использовать in_:

session.query(ZKUser).filter(ZKGroup.id.in_([1,2])).all()