Flask SQLAlchemy syntax for SELECT statement with inner join

Question

I am fairly new to flask and SQLalchemy and struggling with translating a SELECT statement to a Query. I would appreciate any help.

This is the statement:

select * from product_store inner join my_store on product_store.storeid = my_store.storeid where product_store.productid = 1 and my_store.userid = 2 order by product_store.price, my_store.priority;

This is my initial attempt:

productstore = ProductStore.query.join(MyStore, ProductStore.storeid==MyStore.storeid).filter_by(ProductStore.productid==3, MyStore.userid ==2).order_by(ProductStore.price).first()

I am getting "TypeError: filter_by() takes 1 positional argument but 3 were given"

Have a look at ["Querying with Joins"](https://docs.sqlalchemy.org/en/latest/orm/tutorial.html#querying-with-joins) in the official tutorial. — Ilja Everilä, Nov 18 '18 at 19:22
Thanks for the link, Ilja. I have updated the question to be more specific on when I am getting stuck — CheapB, Nov 18 '18 at 22:05
I think you'll find ["What's the difference between filter and filter_by in SQLAlchemy?"](https://stackoverflow.com/questions/2128505/whats-the-difference-between-filter-and-filter-by-in-sqlalchemy) useful. Also ["SqlAlchemy. TypeError: filter_by() takes exactly 1 argument (2 given)"](https://stackoverflow.com/questions/24762664/sqlalchemy-typeerror-filter-by-takes-exactly-1-argument-2-given). — Ilja Everilä, Nov 18 '18 at 22:21
Thank you so much. This is exactly what I needed and I am all set. The query is now: productstore = ProductStore.query.join(MyStore, ProductStore.storeid==MyStore.storeid).filter(ProductStore.productid==3).filter(MyStore.userid ==2).order_by(ProductStore.price).first() — CheapB, Nov 18 '18 at 22:37

score 1 · Accepted Answer · answered Nov 19 '18 at 00:14

filter_by() used with keyword argument expression.

The keyword expressions are extracted from the primary entity of the query, or the last entity that was the target of a call to Query.join().

so you need write as:

productstore = ProductStore.query.filter_by(productid=3) \
                                 .join(MyStore, ProductStore.storeid==MyStore.storeid) \
                                 .filter_by(userid=2) \
                                 .order_by(ProductStore.price).first()

filter() used with SQL expression, can take field compare's result into argument.

in SQL expression you can write:

productstore = ProductStore.query.join(MyStore, ProductStore.storeid==MyStore.storeid) \
                           .filter(ProductStore.productid==3, 
                                   MyStore.userid ==2) \
                           .order_by(ProductStore.price).first()

score 0 · Answer 2 · answered Nov 18 '18 at 22:40

0

With Ilja's help I corrected the query.

        productstore = ProductStore.query.join(MyStore, ProductStore.storeid==MyStore.storeid).filter(ProductStore.productid==3).filter(MyStore.userid ==2).order_by(ProductStore.price).first()

answered Nov 18 '18 at 22:40

CheapB

21
1
5

Flask SQLAlchemy syntax for SELECT statement with inner join

2 Answers2