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?
--
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.
