I'm trying to setup a complicated query that needs to use a correlated subquery
in the WHERE clause and I can't find the right incantation for SA to generate
the query I want.
A simplified example is that I have a table, lets call it 'daily' which has
daily entries in it for a number of entities and another table 'yearly' which
has, surprisingly, yearly entries for each of the entities in it. However the
yearly dates are not uniform and can in fact have two entries per year, so I'm
needing to pick the latest entry for each year that is less than or equal to the
current date of the daily entry (so I don't get the 2006 yearly entry when I'm
looking at a 2005 daily entry for example).
firstly, what I need is something along the lines of
SELECT daily.code from daily d, yearly y1
WHERE y1.id = d.id
AND y1.date =
(SELECT max(y2.date)
FROM yearly y2
WHERE y2.date <= d.date
AND (date_part('year', d.date) - date_part('year', y2.date) <= 1))
which I can achieve via text() w/o any problem, but if I try to assemble the
query using SA, via select and alias, it always adds the aliased table y2 to the
outer query FROM statement, causing Postgres to throw an error because the inner
query is using an aggregate, i.e. the SQL from
yearly_2 = yearly_tbl.alias('yearly_2')
s = select([func.max(yearly_2.c.date))], and_(yearly_2.c.date <= daily.c.date,
func.date_part('year', daily.c.date) - func.date_part('year', yearly_2.c.date)
<= 1))
...
ends up as:
SELECT daily.code from daily d, yearly y1, yearly y2
WHERE y1.id = d.id
AND y1.date =
(SELECT max(y2.date)
WHERE y2.date <= d.date
AND (date_part('year', d.date) - date_part('year', y2.date) <= 1))
which is not valid (at least in postgres).
So my first question is: is there a way to force the inner query to be fully
self-contained, including the FROM?
Secondly, the real criteria is (much) more complicated again and I need to be
able to look up 2 yearly entries, the most recent and one from 5 years prior to
the most recent which means I need a second inner query to find the older entry,
so, the solution for the first question needs to work for at least one more
inner query.
Any assistance gratefully accepted.
Robert
-------------------------------------------------------
This SF.Net email is sponsored by xPML, a groundbreaking scripting language
that extends applications into web and mobile media. Attend the live webcast
and join the prime developer group breaking into this new coding territory!
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642
_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
