On Aug 25, 2014, at 6:07 PM, Anthony Phan Le <pha...@amyris.com> wrote:
> 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 andrun.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:
you need to put an explicit label on "max":
query(func.max(Run.start_time).label('max_start_time'), ...)
then that's your column name:
subquery.c.max_start_time
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
