0

Using Flask-SQLAlchemy, I have two tables - basket and product.

**basket**
id
basket_type
total_amount
created

**product**
id
product_type
price
basket_id
created

I am trying to query the basket with more than "some_number" products. How to do it in one database query?

I tried to do it just like that:

results = db.session.query(Basket, func.count(Product.basket_id)).\
         join(Product, Basket.id == Product.basket_id).group_by(Product.basket_id).all()

I'm getting a list of tuples like:

[(<basket 1>, 25), (<basket 2>, 10)...]

then:

for res in results:
    if res[1] > 'some_number':
       print(res[0].id, res[0].basket_type) # actually, the basket append in list for next using in flask-app

How to do it in one database query?? I want the check to be performed in the request immediately.

davidism
  • 121,510
  • 29
  • 395
  • 339
absent 63
  • 1

3 Answers3

0

You could use the following.

results = basket.query.filter(basket.total_amount>=DESIRED_NUMBER_OF_PRODUCTS).all()

Where basket is the model class name (as defined in models.py)

Read more about Flask-SQLAlchemy query comparisions Here

From your question, I believe this is what you are trying to achieve; I am trying to query the basket with more than "some_number" products

Lenny Ng'ang'a
  • 43
  • 1
  • 6
0
Model.filter(Model.boolean_column == True, Model.string_column == 'Name', Model.int_column == 123)

Model.filter(Model.boolean_column == True, Model.string_column == 'Name', Model.date_column.between(date1,date2)).all()

Model.filter(Model.boolean_column == True, Model.string_column == 'Name', Model.date_column.between(date1,date2)).first()

If you use all() or firts() method, returns list, if you don't use all() or something, returns BaseQuery.

Ayse
  • 576
  • 4
  • 13
0

After several tries I figured out:

  1. If we use group_by(), we should use having() for comparisions.
  2. Query that I needed turned out like this:
results = db.session.query(Basket).\
         join(Product, Basket.id == Product.basket_id).\
         group_by(Product.basket_id).\
         having(func.count(Product.basket_id) > 'some number').all()

I got: result -> [<basket 1>, <basket 3>...] # list of BaseQuery

absent 63
  • 1