query many-many with asssociation table

[Jason Hoppes]

I want to select all users in a particular group. I have a users table, user_groups table, and a users_group_users_asc table to associate the two. Note this is not a self referencing relationship there are three different tables involved not two like the example in the documentation. I have the following configuration:

user_groups_users = Table('user_groups_users_asc', Base.metadata,
                          Column('user_group_id', ForeignKey('user_groups.id', ondelete='CASCADE')),
                          Column('user_id', ForeignKey('users.id', ondelete='CASCADE'))
                    )

class User(Base):
    __tablename__ = 'users'
    id_ = Column('id', BigInteger, primary_key=True)
    username = Column('username', String(255))
    user_groups = relationship('UserGroup',
                               secondary=user_groups_users,
                               back_populates='users)

class UserGroup(Base):
    __tablename__ = 'user_groups'
    id_ = Column('id', BigInteger, primary_key=True)
    group_name = Column('group_name', String(255), nullable=False)
    description = Column('description', Text)
    users = relationship('User',
                         secondary=user_groups_users,
                         back_populates='user_groups',
                         passive_deletes=True)

As I suspected the following query gives me an error:

session.query(User).join(UserGroup).filter(UserGroup.group_name == grp_name).all()

Don't know how to join to <Mapper at 0x7f1ead4604f0; UserGroup>. Please use the .select_from() method to establish an explicit left side, as well as providing an explicit ON clause if not present already to help resolve the ambiguity.

Thank you in advance for your help.

- Jason

[Simon King]

I think it should work if you join to the *relationship* explicitly

ie.

session.query(User).join(User.user_groups).filter(...)

Hope that helps,

Simon

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
http://www.sqlalchemy.org/
 
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/43edd22c-7eca-427b-907e-57e20d665f6en%40googlegroups.com.

[Hoppes, Jason]

Thank you Simon you solved my problem

You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/iMWS9o8vcmw/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAFHwexdfe3cz%2BFxiLLzAHhAswb%2BY1WEKf8ACxqYoDYU5qExK5g%40mail.gmail.com.

[Jason Hoppes]

Thanks Simon this worked.

- Jason