On Apr 30, 2014, at 8:37 AM, gbr <[email protected]> wrote: > For some reason, an exists() where clause which is meant to refer to an outer > element is pulling the outer element's table into the query. > > What I need is as follows > > SELECT anon1.id, anon1.value from ( > SELECT DISTINCT ON (pp.id) pp.id AS id, pp.rev_id AS rev_id, pp.deleted > FROM prod as pp > WHERE (( > select max (revision_id) FROM rev1 > WHERE exists ( > select 1 from prod where pp.id = prod.id > ) > ) = pp.rev_id and pp.deleted = false > ) ORDER BY pp.id, pp.rev_id DESC > ) as anon1 > > The problem is when I create the > > exists().where(pp.id == prod.id) > > part which renders into > > exists (select 1 from prod as prod_1, prod as pp where pp.id = prod_1.id) > > which is not the same any more. How can I prevent SQLA from doing so (I tried > from_obj argument, played around with correlate, tried exists(select), but > none of it worked)? Also, it seems in the inner-most where clause (exist), I > actually need an alias to the 2nd select (the select distinct), which I only > get once the query is created. How can I get this translated to SQLA code?
when you see the "from x, y" pattern it usually means the statement is referring to columns with the wrong parent object in some way, or that correlation is not taking effect as expected. The exists() object and the underlying select() object should always "correlate" automatically, that is if the SELECT is against "x, y" and you place that SELECT embedded into another query within the columns or WHERE clause that is also querying "x", "x" will be removed from the FROM list of the inner select and it will use correlation. To force the behavior of correlation you can use the correlate() or correlate_except() methods. Check the docs for these. Otherwise please share some very basic model setups in conjunction with very simple code that illustrates how you are trying to produce this query (just a "print query" is suitable, no database is needed). -- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
