On May 10, 1:53 pm, Michael Bayer <[EMAIL PROTECTED]> wrote:
> the second version is not going to work because you are expressing
> the joins and foreign keys in terms of the underlying tables, not the
> relations to which the mappers are bound (i.e. T1_select,
> T2_select). so the mapper cant do anything with that (more
OK. I guess I find it surprising because I expeced that:
T1.c.id is T1_select.c.id
That is, I expected the select to just reference the Column objects
from the table, not to have copies of them, so I thought the two joins
would be identical.
But now I have another problem - I've fixed my mappers to join
properly, but I'm getting SQL that mssql 2005 chokes on. Here's a
condensed version of my original example:
########
from sqlalchemy import *
meta = MetaData()
T = Table('T', meta,
Column('id', Integer, primary_key=True),
Column('ver', Integer, primary_key=True),
)
maxvers = select(
[T.c.id.label('mvid'), func.max(T.c.ver).label('maxver')],
group_by=[T.c.id],
)
# Try two different ways:
select_1 = select(
columns = [T],
from_obj = [T, maxvers],
whereclause = and_(maxvers.c.mvid==T.c.id,
maxvers.c.maxver==T.c.ver),
).alias('select_1')
select_2 = select(
columns = [T],
from_obj = [join(T, maxvers,
onclause=and_(maxvers.c.mvid==T.c.id, maxvers.c.maxver==T.c.ver))],
).alias('select_2')
class One(object): pass
class Two(object): pass
m1 = mapper(One, select_1)
m2 = mapper(Two, select_2)
e = create_engine('mssql://sa:[EMAIL PROTECTED]/test')
meta.create_all(e)
s = create_session(bind_to=e)
e.echo = True
# Both of the following fail on MSSQL 2005
try:
s.query(m1).select()
except Exception, e:
print e
try:
s.query(m2).select()
except Exception, e:
print e
########
The first error is "Incorrect syntax near the keyword 'WHERE'" for:
SELECT select_1.ver AS select_1_ver, select_1.id AS select_1_id
FROM (SELECT [T].id AS id, [T].ver AS ver
FROM [T], (SELECT [T].id AS mvid, max([T].ver) AS maxver
FROM [T] GROUP BY [T].id)
WHERE mvid = [T].id AND [T].ver = maxver) AS select_1 ORDER BY
select_1.id
The second is "Incorrect syntax near the keyword 'ON'" for:
SELECT select_2.ver AS select_2_ver, select_2.id AS select_2_id
FROM (SELECT [T].id AS id, [T].ver AS ver
FROM [T] JOIN ((SELECT [T].id AS mvid, max([T].ver) AS maxver
FROM [T] GROUP BY [T].id)) ON mvid = [T].id AND [T].ver = maxver) AS
select_2 ORDER BY select_2.id
I think that for the second one, it's just complaining about the
doubled brackets around the nested query. If I paste it into the SQL
Server UI and run it, it works only if I remove the extra set of
brackets.
I have no idea what's wrong with the first one. If I paste that one
into SQL Server UI, it runs correctly, but it rewrites it to this:
SELECT ver AS select_1_ver, id AS select_1_id
FROM (SELECT T.id, T.ver
FROM T INNER JOIN
(SELECT id AS
mvid, MAX(ver) AS maxver
FROM T AS
T_1
GROUP BY id) AS
derivedtbl_1 ON T.id = derivedtbl_1.mvid AND T.ver =
derivedtbl_1.maxver) AS select_1_1
ORDER BY select_1_id
Any idea how I can write my selects to work against SQL server?
Thanks,
Chris Perkins
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
