Thank you Simon you solved my problem
On Wed, Apr 6, 2022 at 6:26 AM Simon King <si...@simonking.org.uk> 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.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 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+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/CAFHwexdfe3cz%2BFxiLLzAHhAswb%2BY1WEKf8ACxqYoDYU5qExK5g%40mail.gmail.com
> <https://groups.google.com/d/msgid/sqlalchemy/CAFHwexdfe3cz%2BFxiLLzAHhAswb%2BY1WEKf8ACxqYoDYU5qExK5g%40mail.gmail.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/CAEkvBWURKWWdEg%2BoREdfxMAXWAf0dhJt67YQev%2BQ6BngQrdawQ%40mail.gmail.com.
