Thanks Simon this worked. - Jason
On Wednesday, April 6, 2022 at 6:26:51 AM UTC-4 Simon King wrote: > 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 > > > On Tue, Apr 5, 2022 at 9:48 PM Jason Hoppes <jason....@stormfish-sci.com> > wrote: > >> 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 >> >> -- >> 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 >> >> <https://groups.google.com/d/msgid/sqlalchemy/43edd22c-7eca-427b-907e-57e20d665f6en%40googlegroups.com?utm_medium=email&utm_source=footer> >> . >> > -- 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+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/f9804fdc-2e9e-42b2-8de0-529d7b9ff7f7n%40googlegroups.com.