How do I join a subquery using labels for columns that represent aggregate functions?

[Anthony Le]

Sorry for the multiple attempts at posting. I am not sure if posts are time delayed but I am not seeing what I thought I posted. So I am trying one more time. I am trying to convert the following SQL statement into an Sqlalchemy ORM (Version 0.7.4) statement: 

SELECT [hermes_stage].[dbo].[run].[tank]

      ,[id]

      ,[experiment_id]

      ,[local_id]

      ,[start_time]

      ,[stop_time]

  FROM [some_db].[run]

  INNER JOIN (

        Select max(start_time) as LatestDate, [tank]

        from [hermes_stage].[dbo].[run]

        Group by [tank]) submax ON

        [run].start_time = submax.LatestDate

  WHERE some_condition = True and start_time is not NULL and stop_time is NULL and run.tank in ('some list of labels')

  ORDER BY tank 

Note the parts highlighted in yellow, labeling max(start_time), and the part that joins the subquery with the main query. Strategy: I was planning on using a subquery() to generate the query within the inner join.

sub_query = model.session.query(func.max(Run.start_time), Run.tank)

                         .filter(Run.tank.in_(['Q2_A1', 'Q2_A2']))

                         .group_by(Run.tank)

                         .with_labels()

                         .subquery(name = 'sub')

Then: 

Some_table.query()

          .join(sub_query, Run.start_time==sub_query.c.max_1)

          .filter(some_conditions are true)

          .all()

This will, however, fail with an “AttributeError: max_1”.  I did some more digging and found that: 

In: for item in test.c: print item

Out:

sub.max_1

sub.run_tank

I can do the following: 

In: test.c.run_tank

Out: Column(u'run_tank', Unicode(length=10), table=<sub>)

Doing the same for max_1 gives me an attribute error just like above. So the attbribute error is caused by trying to access this column. When I print the types of each item in c, I get the following. 

In:for item in test.c: print type(item)  

Out:

<class 'sqlalchemy.sql.expression.ColumnClause'>

<class 'sqlalchemy.schema.Column'>

I think I am getting the error because it is not possible to retrieve a column name from something of type “ColumnClause”. Two questions come to mind:1.) How do I convert a ColumnClause into a “Column”? I think I need to do this so that I can perform the join part correctly. 2.) What alternative querying strategy would you recommend in the absence of a solution to 1.)? 

[Jonathan Vanasco]

I don't have any code handy, but IIRC, I've just used 'label' to assign a column name to the subquery, then address it via '.c.' like you did in the second part

    sub_query = model.session.query(

         func.max(Run.start_time).label('start_time'), 

         Run.tank.label('tank')

     )

and

         sub_query, Run.start_time==sub_query.c.start_time

[Simon King]

I'm not certain, but you might be able to use:

func.max(Run.start_time).label('max_start_time')

and refer to it as:

sub_query.c.max_start_time

Hope that helps,

Simon