CODEWITHSUNDEEP
pythonsqlalchemyflaskflask-sqlalchemy
aNgeLyN omar
aNgeLyN omar

Reputation: 43

Select with count in Flask-SQLAlchemy

I'm still confused as to how SQLAlchemy works. As of now I have a query which looked like this

SELECT cast(a.product_id as bigint) id, cast(count(a.product_id) as bigint) itemsSold, cast(b.product_name as character varying)
    from transaction_details a 
    left join product b
    on a.product_id = b.product_id
    group by a.product_id, b.product_name
    order by itemsSold desc;

I'm not so sure how this is converted in Flask tho.

Upvotes: 1

Views: 2067

Answers (1)

Audrius Kažukauskas
Audrius Kažukauskas

Reputation: 13533

If you're new to SQLAlchemy, read both Object Relational Tutorial and SQL Expression Language Tutorial to get familiar with the way it works. Since you are using Flask-SQLAlchemy extension, keep in mind that a lot of names, which are imported in the examples of aforementioned tutorials, can be accessed via the instance of SQLAlchemy class (usually named db in Flask-SQLAlchemy examples). Your SQL query, when converted to SA, would look something like the following (untested):

# Assuming that A and B are mapped objects that point to tables a and b from
# your example.
q = db.session.query(
    db.cast(A.product_id, db.BigInteger),
    db.cast(db.count(A.product_id), db.BigInteger).label('itemsSold'),
    db.cast(B.product_name, db.String)
# If relationship between A and B is configured properly, explicit join
# condition usually is not needed.
).outerjoin(B, A.product_id == B.product_id).\
group_by(A.product_id, B.product_name).\
order_by(db.desc('itemsSold'))

Upvotes: 3

Related Questions