Using alias on column in sqlalchemy and use that alias in group_by

Asked May 05 '16 at 21:32

Active May 05 '16 at 22:23

Viewed 641 times

I am trying to convert an SQL to sqlalchemy.

SELECT * FROM (SELECT user_id, GROUP_CONCAT(distinct product_code) as prod FROM purchase_detail 
GROUP BY user_id )  tab WHERE tab.prod ='000'

With the information in this link I tried to come up with this.

db_session.query(PurchaseDetail.user_id, 
           func.group_concat(PurchaseDetail.product_code.label("code")).
           group_by(PurchaseDetail.user_id).filter_by(code='000')

This obviously throws an error saying purchase_detail doesn't have a column called code. I kind of knew the label would not work here. How can I can give an alias to that grouped column and filter on that column?

edited May 23 '17 at 10:33

Community

asked May 05 '16 at 21:32

user3527975

1,683
8
25
43

The original SQL won't work. The `tab` subquery doesn't return a `produce_code` column, it returns `prod`. – Barmar May 05 '16 at 22:18
@Barmar : My bad. Editing question now. Thanks for pointing. – user3527975 May 05 '16 at 22:23

Using alias on column in sqlalchemy and use that alias in group_by

0 Answers0