CODEWITHSUNDEEP
pythonsqlalchemy
zerocool
zerocool

Reputation: 833

is it possible to use or_ between filter_by

I have this query, to check if players are in one or the other column

SELECT event_id
FROM schedule
WHERE player1 = 1 and player2 = 2 or player1 = 2 and player2 = 1

This is working fine, however I need to write this in SQLAlchemy, so I came up with following:

check_events = db.session.query(Schedule.event_id).filter(and_(Schedule.player1==form.player1.data, Schedule.player2==form.player2.data)).filter(and_(Schedule.player1==form.player2.data, Schedule.player2==form.player1.data))

The problem is that when i use another filter or filter_by the resulting query will have AND instead OR operator

SELECT "Schedule".event_id AS "Schedule_event_id"
FROM "Schedule"
WHERE "Schedule".player1 = ? AND "Schedule".player2 = ? AND "Schedule".player1 = ? AND "Schedule".player2 = ?

I also feel that my initial query can be ugly and it can be done in different way. Even though, is it possible to use or_ 'between' filter?

Upvotes: 0

Views: 47

Answers (1)

SuperShoot
SuperShoot

Reputation: 10871

You want to combine or_ and and_ and pass the result to a single filter(). For example:

from sqlalchemy import and_, or_

(
    db.session.query(Schedule.event_id)
    .filter(
        or_(
            and_(
                Schedule.player1 == form.player1.data,
                Schedule.player2 == form.player2.data,
            ),
            and_(
                Schedule.player2 == form.player1.data,
                Schedule.player1 == form.player2.data,
            ),
        )
    )
)

These types of expressions can only be passed to Query.filter(). Query.filter_by() only accepts keyword arguments and transforms them into equality expressions (==).

Upvotes: 1

Related Questions