Nested SELECT using sqlalchemy

Question

I would like to create a query with nested SELECT using sqlalchemy, but I cannot get the expected result.

I of course simplified the following query so it will be easilly understandable for this post.

Here is the query I would like to construct:

SELECT pear_table.size,
       (SELECT MIN(apple.apple_date)
        FROM apple
        WHERE apple_id = pear_table.pear_id ) apple_min,
       (SELECT max(lemon.lemon_date)
        FROM lemon
        WHERE lemon_id = pear_table.pear_id ) lemon_max
FROM
  (SELECT pear_id
   FROM pear
   WHERE pear_color = 'green') pear_table

Notice that I use 'pear_id' in both of my subqueries. This query works well when used as a string.

Now I try to construct it using sqlalchemy:

APPLE = wrapper.getMapper('apple')
LEMON = wrapper.getMapper('lemon')
PEAR = wrapper.getMapper('pear')

pear_table = select([PEAR.apple_id])
pear_table.append_whereclause(PEAR.pear_color == 'green')

apple_min = select([func.min(APPLE.apple_date).label('apple_min')])
apple_min.append_whereclause(APPLE.apple_id == pear_table.pear_id)

lemon_max = select([func.min(LEMON.apple_date).label('lemon_max')])
lemon_max.append_whereclause(LEMON.lemon_id == pear_table.pear_id)

main_query = select([pear_table.c.pear_id,
                     apple_min.c.apple_min,
                     lemon_max.c.lemon_max])

And here is what sqlalchemy construct with this code:

SELECT pear_table.size,
       apple_min,
       lemon_max
FROM
  (SELECT pear_id
   FROM pear
   WHERE pear_color = 'green') pear_table,
  (SELECT MIN(apple.apple_date)
   FROM apple
   WHERE apple_id = pear_table.pear_id ) apple_min,
  (SELECT max(lemon.lemon_date)
   FROM lemon
   WHERE lemon_id = pear_table.pear_id ) lemon_max

The problem is that 'pear_id' is unreachable by my 2 subqueries 'apple' and 'lemon', because sqlalchemy put the subqueries in the 'FROM' clause.

I tried to fix my problem using the correlate option:

.
apple_min = select([func.min(APPLE.apple_date).label('apple_min')]).correlate(None)
.
lemon_max = select([func.min(LEMON.apple_date).label('lemon_max')]).correlate(None)
.

Here is what I get:

SELECT pear_table.size,
       apple_min,
       lemon_max
FROM
  (SELECT pear_id
   FROM pear
   WHERE pear_color = 'green') pear_table,
  (SELECT MIN(apple.apple_date)
   FROM apple,
        (SELECT pear_id
         FROM pear
         WHERE pear_color = 'green')
   WHERE apple_id = pear_table.pear_id ) apple_min,
  (SELECT max(lemon.lemon_date)
   FROM lemon,
        (SELECT pear_id
         FROM pear
         WHERE pear_color = 'green')
   WHERE lemon_id = pear_table.pear_id ) lemon_max

This duplicates the 'FROM pear_id ...', and I don't want duplicate queries for obvious performance reasons.

• Am I doing something wrong?
• Is there a better way to construct my query?
• Is sqlalchemy allow me to construct the way I want?
• Is there a way to put the subqueries directly in the main 'SELECT' clause?

I am currently using sqlalchemy 0.4, but I tried the same thing with 0.8 and the result is the same.

Answer 1

Try this instead (key is to use Scalar Selects):

pear_table = (
    select([PEAR.pear_id])
    .where(PEAR.pear_color == 'green')
)

apple_min = (
    select([func.min(APPLE.apple_date).label('apple_min')])
    .where(APPLE.apple_id == pear_table.c.pear_id)
).as_scalar()

lemon_max = (
    select([func.min(LEMON.lemon_date).label('lemon_max')])
    .where(LEMON.lemon_id == pear_table.c.pear_id)
).as_scalar()

main_query = select([pear_table.c.pear_id, apple_min, lemon_max])
print(main_query)