如何查询包含user_x的所有'foo'类型的组? (多对多表) [英] How to query for all groups of type 'foo' that contain user_x? (many-to-many table)

问题描述

给出下表，如何查询user_x的所有组类型'foo'的组?

Given the following tables below, how do I query for all groups of grouptype 'foo' for user_x?

等效的SQL类似于:

SELECT * FROM users_to_groups
    LEFT JOIN users ON user_id=users.id
    LEFT JOIN groups ON group_id=groups.id
    WHERE groups.type='type1' AND user_id=1;

我当时在想SQLAlchemy查询看起来像这样:

I was thinking the SQLAlchemy query would look something like:

session.query(UserGroup).filter(UserGroup.user==user_x, 
                                UserGroup.group.grouptype=='foo')

但我不知道如何指定组类型(上面的查询引发了此异常:AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' object associated with UserGroup.group has an attribute 'grouptype')

but I don't know how to specify the grouptype (the above query raises this exception: AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' object associated with UserGroup.group has an attribute 'grouptype')

users_to_groups = Table(
    'users_to_groups', Base.metadata,
    Column('user_id', Integer, ForeignKey('users.id'), primary_key=True),
    Column('group_id', Integer, ForeignKey('groups.id'), primary_key=True),
)

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    groups = relationship('Group',
                          secondary=users_to_groups,
                          backref=backref('users',
                                          collection_class=set),
                          collection_class=set)

class Group(Base):
    __tablename__ = 'groups'
    id = Column(Integer, primary_key=True)
    grouptype = Column(String)

Base.metadata.create_all(engine)

class UserGroup(object):
    pass

mapper(UserGroup, users_to_groups,
        properties={
            'group' : relationship(Group),
            'user' : relationship(User),
})

我将sqlalchemy 0.8.2与Postgres 9.2.4结合使用.

I'm using sqlalchemy 0.8.2 with Postgres 9.2.4.

推荐答案

您可以使用

You can use RelationshipProperty.Comparator.has():

session.query(UserGroup).filter(UserGroup.user == user_x,
                                UserGroup.group.has(Group.grouptype == 'foo'))

您可能会发现直接查询Group更自然:

You may find it more natural to query for Group directly:

session.query(Group).filter(Group.users.any(User.id == user_x), 
                            Group.grouptype == 'foo')

这篇关于如何查询包含user_x的所有'foo'类型的组? (多对多表)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！