correlate_except(table) did the trick. I thought I had tried it before, but something must have gone wrong. Now it works.
Thanks for your help. On Wednesday, April 30, 2014 11:53:04 PM UTC+10, Michael Bayer wrote: > > > On Apr 30, 2014, at 8:37 AM, gbr <[email protected] <javascript:>> > 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.
