SQLAlchemy: Join to subquery with no from field

Question

I have a table called product_model with its corresponding ProductModel SQLAlchemy model.

I wish to join the product_model table to a select sub query which simply unnests two PostgreSQL arrays (product model ids, and quantity) and then join the product_model table to this data. The data is taken from a simple cart (a python dict). I am using PostgreSQL for this use case, however if a better idea exists I will be more than happy to use an alternative.

In SQL this would look as follows (this statement calculates the total mass of all products in the cart):

SELECT SUM(p.mass * c.quantity) FROM product_model AS p
INNER JOIN (
  SELECT UNNEST(ARRAY[1]) AS model_id, UNNEST(ARRAY[2]) AS quantity
) AS c ON p.id = c.model_id
GROUP BY p.id;

I would like to model this SQL statement in SQLAlchemy. Is this possible? I'm not sure how to join to a subquery which doesn't select from a table. Or will I need to search for an alternative?

Audrius Kažukauskas · Accepted Answer · 2013-12-29 10:25:33Z

5

Not sure how good your solution is, but here's how to write your SQL query using SQLAlchemy:

from sqlalchemy.dialects.postgresql import array

# First write the subquery.  array() function is used for Pg ARRAY literals.
subq = db.session.query(
    db.func.unnest(array([1])).label('model_id'),
    db.func.unnest(array([2])).label('quantity')
).subquery()

# Now when you have the subquery, you can use it as if it were a Table object.
q = db.session.query(db.func.sum(ProductModel.mass * subq.c.quantity)).\
    join(subq, ProductModel.id == subq.c.model_id).\
    group_by(ProductModel.id)

answered Dec 29, 2013 at 10:25

Audrius Kažukauskas

13.6k4 gold badges56 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQLAlchemy: Join to subquery with no from field

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related