I think I just figured it out. The join clause needs to go to the from_obj
table...
sel = select(
columns=[e2.c.id, func.max(e2.c.other_id)],
from_obj=other.join(
e2, e2.c.other_id == other.c.id
),
group_by=[e2.c.id]
)
e1 = sel
This renders the query as expected. This behaviour fairly unexpected
though. Is this somehow mentioned in the documentation? An example like
this would be great (if not already existing)...
On Tuesday, May 6, 2014 7:07:16 PM UTC+10, gbr wrote:
>
> 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.
