On Thu, Apr 30, 2020, at 8:17 AM, Marat Sharafutdinov wrote:
> from sqlalchemy import Column, ForeignKey, Integer, create_engine
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm import relationship, sessionmaker
>
> Base = declarative_base()
>
> class User(Base):
> __tablename__ = 'users'
> id = Column(Integer, primary_key=True)
> groups = relationship('UserGroup', lazy='selectin')
>
> class Group(Base):
> __tablename__ = 'groups'
> id = Column(Integer, primary_key=True)
>
> class UserGroup(Base):
> __tablename__ = 'user_groups'
> user_id = Column(Integer, ForeignKey(User.id), primary_key=True)
> group_id = Column(Integer, ForeignKey(Group.id), primary_key=True)
> group = relationship(Group, lazy='joined', innerjoin=True)
>
> engine = create_engine('sqlite:///:memory:', echo=True)
> Base.metadata.create_all(engine)
> Session = sessionmaker(bind=engine)
> session = Session()
>
> users = (
> session.query(User)
> .filter(User.groups.any(UserGroup.group.has(Group.id.in_((1, 2)))))
> .all()
> )
>
> I need to get all users which are in groups with ids 1 or 2. This query
> generates the following SQL:
>
> SELECT users.id AS users_id
> FROM users
> WHERE EXISTS (SELECT 1
> FROM user_groups
> WHERE users.id = user_groups.user_id AND (EXISTS (SELECT 1
> FROM groups
> WHERE groups.id = user_groups.group_id AND groups.id IN (1, 2))));
>
> The question is how can "joined" relationship between UserGroup and Group be
> involved?
that query uses a bunch of correlation which is not efficient but it should get
you the right result.
if you want to use a join, query.join() gets you that:
query(User).join(User.groups).join(UserGroup.group).filter(Group.id.in_([1, 2]))
>
> --
> 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/76a7551d-200e-4fbc-920e-e313e1ec50d6%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/76a7551d-200e-4fbc-920e-e313e1ec50d6%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/c1cb1497-32e8-43c4-b37d-591e2511bad3%40www.fastmail.com.
