CODEWITHSUNDEEP
pythonsqlalchemy
Jack Stouffer
Jack Stouffer

Reputation: 369

SQLAlchemy: filtering multiple column values in a subquery

In SQL, I can use the IN operator with a subquery like so:

SELECT * FROM t1
WHERE (t1.some_int, t1.some_string) IN (
    SELECT id, name FROM t2
)

But I am unable to translate this to an SQLAlchemy query. As far as I know, the in_ method only works on one column. Is there any way to replicate this functionality in SQLAlchemy?

Upvotes: 0

Views: 957

Answers (1)

Anton Glukhov
Anton Glukhov

Reputation: 3707

You could use JOIN instead of subquery. Something like this: 

SELECT * FROM t1 INNER JOIN t2 ON t1.some_int = t2.id AND t1.some_string = t2.name

And in sqlalchemy:

T1:

class T1(DeclarativeBase):
    __tablename__ = 't1'

    __table_args__ = {'mysql_engine': 'InnoDB'}

    id = Column(u'id', Integer, primary_key=True)
    some_int = Column('some_int', Integer)
    some_str = Column('some_str', String(45))

    def __init__ (self, some_int, some_str):
        self.some_int = some_int
        self.some_str = some_str

T2:

class T2(DeclarativeBase):
    __tablename__ = 't2'

    __table_args__ = {'mysql_engine': 'InnoDB'}

    id = Column(u'id', Integer, primary_key=True)
    name = Column('name', String(45))
    data = Column('data', String(45))

    def __init__ (self, name, data):
        self.name = name
        self.data = data

In source code:

data = session.query(T1).join(T2, and_(T1.some_int == T2.id, T1.some_string == T2.name)).all()

In result engine generates sql:

SELECT t1.some_int AS t1_some_int, t1.id AS t1_id FROM t1 INNER JOIN t2 ON t1.some_int = t2.id AND t1.some_str = t2.name

Upvotes: 1

Related Questions