I'm running into a similar problem again. This time, correlate_except doesn't change anything.
Consider this example from sqlalchemy import * metadata = MetaData() product = Table('product', metadata, Column('id', Integer), Column('other_id', Integer)) other = Table('other', metadata, Column('id', Integer)) e2 = product.alias('e2') sel = select( columns=[e2.c.id, func.max(e2.c.other_id)], from_obj=other, group_by=[e2.c.id], correlate=False # doesn't make a difference ).correlate_except(product, e2) # doesn't make a difference either e1 = sel.join( e2, e2.c.other_id == other.c.id ) print e1 # (SELECT e2.id AS id, max(e2.other_id) AS max_1 # FROM product AS e2, other GROUP BY e2.id) JOIN product AS e2 ON e2.other_id = other.id # What I want is # # SELECT e2.id, max(e2.other_id) # FROM other # JOIN product as e2 on e2.other_id = other.id # GROUP BY e2.id How can I get SQLA to create the query I want? On Thursday, May 1, 2014 8:20:30 AM UTC+10, gbr wrote: > > 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 <doub...@directbox.com> 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 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.