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.