CODEWITHSUNDEEP
pythonsqlalchemy
Aayush Karki
Aayush Karki

Reputation: 841

getting a list after grouping in sqlalchemy

I have a table Food. It has fields: cust_name, phone_number , order_date

I am trying to build a dictionary where a key of pair (cust_name, phone_number) gives a list of order_date. For that I need to query appropriately in sqlalchemy. I'm using Postgres.

So far I have:

db.session.query(Food.cust_name, Food.phone_number).group_by(Food.cust_name, Food.phone_number).all()

What do I need to change so that I get a corresponding list of order_date

Upvotes: 0

Views: 67

Answers (1)

Ilja Everilä
Ilja Everilä

Reputation: 52949

Use the array_agg() aggregate function to produce a list of order dates:

res = db.session.query(Food.cust_name,
                       Food.phone_number,
                       db.func.array_agg(Food.order_date).label('order_dates')).\
    group_by(Food.cust_name, Food.phone_number).\
    all()

the_dict = {(r.cust_name, r.phone_number): r.order_dates for r in res}

Upvotes: 1

Related Questions