joining/aliasing problems

[Daniel]

Hi,

I'm trying to translate this sql expression:

(select max(id) as max_id from votes group by user_id, idea_id) as m
left join votes on m.max_id=votes.id

Into python code. So far I have:

s = Session()
q =
s.query(sa.func.max_(Vote.id).label('max_id')).group_by(Vote.idea_id,
Vote.user_id)

This is where I get stuck. If I just wanted a join I could do:

s = Session()
q =
s.query(sa.func.max_(Vote.id).label('max_id')).group_by(Vote.idea_id,
Vote.user_id).subquery()
s.query(Vote).join((q, q.c.max_id==Vote.id))

However, because I need an outer join the statements are back-to-
front. A right join would work although I think you left them out on
idealogical grounds. If I try reversing the clauses I get SQL errors
due to lack of aliasing. Do you have any ideas? (I'm working on mysql)

Thanks,

Daniel

[Daniel]

I've managed to fix it using sqalchemy.orm.outerjoin in conjunction
with query's select_from function. Out of interest, why is the naming
scheme different for these 2 functions?

Thanks,

Daniel

[Michael Bayer]

you'd probably want to use select_from() in conjunction with orm.outerjoin(). its the last example at http://www.sqlalchemy.org/docs/ormtutorial.html#querying-with-joins .

you also might be able to say query(Vote).outerjoin((Vote, subquery.c.max_id==Vote.id)).

not sure what the "lack of aliasing" error is, subquery() returns an Alias() object.

[Michael Bayer]

which two names/functions are you referring to ?

>
> Thanks,
>
> Daniel
>
> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>

[Daniel]

> > I've managed to fix it using sqalchemy.orm.outerjoin in conjunction
> > with query's select_from function. Out of interest, why is the naming
> > scheme different for these 2 functions?
>
> which two names/functions are you referring to ?
>

outerjoin and select_from, one seems to be lower case, the other,
lower case with underscores. Is this because one is a function and the
other is a method?

unfortunately this:

query(Vote).outerjoin((Vote, subquery.c.max_id==Vote.id))

generates invalid SQL and this:

query(Vote).outerjoin((subquery, subquery.c.max_id==Vote.id))

has the join backwards, as ordering counts. I was trying things like:

subquery.outerjoin((Vote, subquery.c.max_id==Vote.id))

This generates the rather indecipherable error "AttributeError:
'_TextClause' object has no attribute 'foreign_keys'". If you try
doing the same query, but don't generate a subquery first the error is
"Not unique table/alias: 'votes'".

But as I say I found a workaround using the orm.outerjoin function, so
this is rather academic from my perspective.

Thanks,

Daniel

[Michael Bayer]

On Jul 11, 2010, at 5:40 AM, Daniel wrote:

>
>>> I've managed to fix it using sqalchemy.orm.outerjoin in conjunction
>>> with query's select_from function. Out of interest, why is the naming
>>> scheme different for these 2 functions?
>>
>> which two names/functions are you referring to ?
>>
>
> outerjoin and select_from, one seems to be lower case, the other,
> lower case with underscores. Is this because one is a function and the
> other is a method?

we use lowercase with underscores for all names, but the underscores aren't applied to words that colloquially tend to be one word like "outerjoin".

>
> unfortunately this:
>
> query(Vote).outerjoin((Vote, subquery.c.max_id==Vote.id))
>
> generates invalid SQL and this:
>
> query(Vote).outerjoin((subquery, subquery.c.max_id==Vote.id))
>
> has the join backwards, as ordering counts. I was trying things like:
>
> subquery.outerjoin((Vote, subquery.c.max_id==Vote.id))
>
> This generates the rather indecipherable error "AttributeError:
> '_TextClause' object has no attribute 'foreign_keys'".

well in that last example subquery is not a Query() object, its an Alias construct. It has a different API and you should work through the SQL Expression Tutorial to get a feel for expression constructs. It probably shouldn't be making a text() construct out of a tuple, though. ticket 1847

> If you try
> doing the same query, but don't generate a subquery first the error is
> "Not unique table/alias: 'votes'".
>
> But as I say I found a workaround using the orm.outerjoin function, so
> this is rather academic from my perspective.
>
> Thanks,
>
> Daniel
>

[Daniel]

One last question, is it possible to do a full outer join?

Thanks,

Daniel

[Michael Bayer]

you'd have to use unions, or a custom SQL construct if your database happens to support the actual "FULL OUTER JOIN" syntax.