Re: [sqlalchemy] query many-many with asssociation table
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
> 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 . 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
>>
>>
>> .
>>
>
--
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.
Re: [sqlalchemy] query many-many with asssociation table
Thank you Simon you solved my problem
On Wed, Apr 6, 2022 at 6:26 AM 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.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 . 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
>>
>> .
>>
> --
> 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
>
> .
>
--
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.
Re: [sqlalchemy] query many-many with asssociation table
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
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 . 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
>
> .
>
--
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.
[sqlalchemy] query many-many with asssociation table
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 . 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.
