CODEWITHSUNDEEP
pythonmysqlsqlalchemy
joshualan
joshualan

Reputation: 2140

How to join two queries in SQLAlchemy?

In this example, I am using the sample MySQL classicmodels database.

So I have two queries:

products = session.query(Products)
orderdetails = session.query(OrderDetails)

Let's assume I cannot make any more queries to the database after this and I can only join these two queries from this point on.

I want to do an outer join on them to be able to do something like this:

for orderdetail, product in query:
    print product.productName, product.productCode, orderdetails.quantityOrdered

However, whenever I do an outerjoin on this, I can only seem to get a left join.

query = orderdetails.outerjoin(Products)

Code like this yields only orderdetails columns:

for q in query:
    # Only gives orderdetails columns
    print q

And doing something like this:

for orderdetails, product in query:
    print orderdetails, product

Gives me an error: TypeError: 'OrderDetails' object is not iterable.

What am I doing wrong? I just want columns from the Products table as well.

EDIT:

I have found my solution thanks to @univerio's answer. My real goal was to do a join on two existing queries and then do a SUM and COUNT operation on them.

SQLAlchemy basically just transforms a query object to a SQL statement. The with_entities function just changes the SELECT expression to whatever you pass to it. This is my updated solution, which includes unpacking and reading the join:

for productCode, numOrders, quantityOrdered in orderdetails.with_entities(
        OrderDetails.productCode, 
        func.count(OrderDetails.productCode),
        func.sum(OrderDetails.quantityOrdered)).group_by(OrderDetails.productCode):
    print productCode, numOrders, quantityOrdered

Upvotes: 2

Views: 4212

Answers (1)

univerio
univerio

Reputation: 20518

You can overwrite the entity list with with_entities():

orderdetails.outerjoin(Products).with_entities(OrderDetails, Products)

Upvotes: 5

Related Questions