CODEWITHSUNDEEP
pythonsqlalchemy
user5617880
user5617880

Reputation: 125

SqlAlchemy how to query column A==a and B==b and A==b and B==a

I have a table that have enemy_one, fight_id and enemy_two.

The thing is, sometimes enemy_two becomes enemy_one and vice versa.

I can do:

session.query(Fight.fight_id).filter(Fight.enemy_one=='Jack', Fight.enemy_two=='Fat Chinese').all()

then:

session.query(Fight.fight_id).filter(Fight.enemy_one=='Fat Chinese', Fight.enemy_two=='Jack').all()

And this way I get all the fights, but there's a way, to bind this two query's together?

Upvotes: 1

Views: 262

Answers (2)

van
van

Reputation: 77012

With the in_ clause:

def get_fights(enemy_one, enemy_two):
    return (
        session
        .query(Fight)
        .filter(Fight.enemy_one.in_([enemy_one, enemy_two]))
        .filter(Fight.enemy_two.in_([enemy_one, enemy_two]))
        .filter(Fight.enemy_one != Fight_enemy_two)
    )

With the or_ clause:

def get_fights(enemy_one, enemy_two):
    return (
        session
        .query(Fight)
        .filter(or_(
            and_(Fight.enemy_one == enemy_one, Fight.enemy_two == enemy_two),
            and_(Fight.enemy_one == enemy_two, Fight.enemy_two == enemy_one),
        ))
    )

Upvotes: 1

wanderlust
wanderlust

Reputation: 1936

Feel free to use sqlalchemy's function or_() with the filter operator in_ like this:

from sqlalchemy import or_

enemies = ['Fat Chinese', 'Jack']

session.query(Fight.fight_id).filter(
    or_(
        Fight.enemy_one.in_(enemies),
        Fight.enemy_two.in_(enemies)
       )).all()

Upvotes: 0

Related Questions