How to use sum and count in a joined query on sqlAlchemy models

Question

I have working SQL that I need to represent in a working Flask app that is using SQLAlchemy as an ORM. I can represent the joins, etc., but am getting nowhere with tying in the sums, counts, and the other specific select element, u.firstname || ' ' || u.lastname.

SQL

select
  u.firstname || ' ' || u.lastname SELLER,
  count(i.id) UNIQUE_ITEMS,
  sum(i.qty) ITEMS_TOTAL,
  sum(i.qty * p.price) ORDER_AMOUNT
from
  orders o
  INNER JOIN order_items i
    on o.id = i.order_id
    and o.campaign_id = 133
  INNER JOIN products p
      ON i.product_id = p.id
  INNER JOIN users u
      ON o.user_id = u.id
GROUP BY u.id
ORDER BY sum(i.qty) DESC, sum(i.qty * p.price) DESC;

and working Flask implementation:

orders = Order.query.filter_by(campaign_id=campaign_id).
  join(OrderItem).
  join(Product).
  join(User).
  order_by(OrderItem.qty.desc())

If I add group_by(User.id) it complains as well that I haven't placed in every other element from Order.

In case the functions are the issue, I'm running against a Postgres 9.5

I would try sqlalchemy.sql.select in order to reproduce the query. — Paulo Scardine
– Paulo Scardine, Commented Jan 4, 2017 at 19:41
one could of course just session.execute(<your sql here>) but I knew that there was a more SQLAlchemy structured way. The accepted answer was what I was anticipating. thx. — Shawn Mehan
– Shawn Mehan, Commented Jan 4, 2017 at 23:43
Wayne's answer is very good. I would not compare session.execute and sqlalchemy.sql.select, the second is much more like session.query, you just chain methods that have the same name of their SQL counterparts. — Paulo Scardine
– Paulo Scardine, Commented Jan 5, 2017 at 13:05

Wayne Werner · Accepted Answer · 2017-01-04 20:49:01Z

You're trying to query the Order, but that's not what you are querying in your raw SQL. You need something like:

query = session.query(
    (User.first_name + ' ' + User.last_name).label('seller'),
    sa.func.count(OrderItem.id).label('unique_items'),
    sa.func.sum(OrderItem.qty).label('items_total'),
    sa.func.sum(OrderItem.qty * Product.price).label('order_amount'),
).join(OrderItem).join(Product).group_by(User.id).order_by('items_total',
                                                           'order_amount')

To put it all together in an example that shows you what's going on:

import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base
from decimal import Decimal

engine = sa.create_engine('sqlite:///:memory:')
Base = declarative_base()
session = sa.orm.sessionmaker(bind=engine)()

class OrderItem(Base):
    __tablename__ = 'order'

    id = sa.Column('id', sa.Integer, primary_key=True)
    product_id = sa.Column('product_id', sa.Integer, sa.ForeignKey('product.id'))
    user_id = sa.Column('user_id', sa.Integer, sa.ForeignKey('user.id'))
    qty = sa.Column('qty', sa.Integer)

class Product(Base):
    __tablename__ = 'product'

    id = sa.Column('id', sa.Integer, primary_key=True)
    price = sa.Column('price', sa.Numeric(14,2))

class User(Base):
    __tablename__ = 'user'

    id = sa.Column('id', sa.Integer, primary_key=True)
    first_name = sa.Column('first_name', sa.Text)
    last_name = sa.Column('last_name', sa.Text)

Base.metadata.create_all(engine)

engine.echo = True

session.add(User(id=42, first_name='John', last_name='Cleese'))
session.add(User(id=13, first_name='Sir', last_name='Robin'))
session.add(Product(id=1, price=Decimal('2.10')))
session.add(OrderItem(product_id=1, user_id=42, qty=9))
session.add(OrderItem(product_id=1, user_id=42, qty=2))
session.add(OrderItem(product_id=1, user_id=13, qty=2))
session.add(OrderItem(product_id=1, user_id=13, qty=3))
session.add(OrderItem(product_id=1, user_id=13, qty=20))
session.commit()

query = session.query(
    (User.first_name + ' ' + User.last_name).label('seller'),
    sa.func.count(OrderItem.id).label('unique_items'),
    sa.func.sum(OrderItem.qty).label('items_total'),
    sa.func.sum(OrderItem.qty * Product.price).label('order_amount'),
).join(OrderItem).join(Product).group_by(User.id).order_by('items_total',
                                                           'order_amount')


print('{0:=^40}\n{1:^40}\n{0:=^40}'.format('=', 'Query'))
results = [row for row in session.execute(query)]
print('{0:=^40}\n{1:^40}\n{0:=^40}'.format('=', 'Results'))
for row in results:
    print(dict(row))

this was superb and thanks for the complete example. I knew that there was a SQLAlchemy structured way to get this done, but couldn't find any examples. This had gotten me further along and hopefully will help others in the future.

Collectives™ on Stack Overflow

How to use sum and count in a joined query on sqlAlchemy models

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related