Reputation: 5187
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
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