Returning a "computed" column from a query in SQLAlchemy

Question

Say I have the following classes:

class Basket(db.Model):
    id = Column('id', Integer, primary_key=True)

class Fruit(db.Model):
    id = Column('id', Integer, primary_key=True)
    basket_id = Column(Integer, ForeignKey('basket.id'))

I would like to do a query on the fruit table that returns among its result a "computed" column called in_basket that is True if a fruit's basket_id is in the list of basket ids [1, 2, 3] and False otherwise.

Is there a way to do this in SQLAlchemy? I'm using flask-sqlalchemy and Postgres 9.

Thanks.

Answer 1

Should be straightforward using case expression:

class Fruit(...):
    ...
    def is_in_basket(self, busket_list):
        return self.basket_id in busket_list

def get_fruits(basket_list):
    from sqlalchemy.sql.expression import case
    xpr = case([(Fruit.basket_id.in_(basket_list), True),], else_ = False).label("in_basket")
    qry = session.query(Fruit, xpr)
    fruits = []
    for fruit, in_basket in qry:
        fruit.in_basket = in_basket # assign value to a Fruit property (as per comment below)
        fruits.append(fruit)
    return fruits

fruits = get_fruits(my_basket_list)
for f in fruits:
    print f, f.in_basket, f.is_in_basket(my_basket_list)