mergesort
mergesort

Reputation: 5187

How to filter based on two columns where one column may not be defined using SQLAlchemy?

I have a ShippingRate table with a minimum_price and a maximum_price. If the minimum is 0 and the max is 49, the query below works if the value is between. If the minimum is $50 and the maximum is not defined, it doesn't work. Can I write a single query that will get the correct result?

db.query(ShippingRate).filter(literal(myVal).between(ShippingRate.minimum, ShippingRate.maximum))

Upvotes: 0

Views: 276

Answers (1)

Penguinfrank
Penguinfrank

Reputation: 392

Assuming if minimum or maximum doesn't exist, there is no maximum or minimum respectively, and if neither exist then all values are true, how about something like this:

from sqlalchemy import and_, or_

db.query(ShippingRate).filter(or_( \
    and_(ShippingRate.minimum == None, ShippingRate.maximum == None), \
    and_(ShippingRate.minimum != None, ShippingRate.maximum != None,  literal(myVal).between(ShippingRate.minimum, ShippingRate.maximum)), \
    and_(ShippingRate.minimum == None, myVal < ShippingRate.maximum), \
    and_(ShippingRate.maximum == None, myVal > ShippingRate.minimum)))

Upvotes: 1

Related Questions