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.hop...@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+unsubscr...@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/CAFHwexdfe3cz%2BFxiLLzAHhAswb%2BY1WEKf8ACxqYoDYU5qExK5g%40mail.gmail.com.
