How to query for all groups of type 'foo' that contain user_x? (many-to-many table)

Question

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

The equivalent SQL would be something like:

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;

I was thinking the SQLAlchemy query would look something like:

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

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),
})

I'm using sqlalchemy 0.8.2 with Postgres 9.2.4.

Answer 1

You can use RelationshipProperty.Comparator.has():

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

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')