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 <[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.
