I'm working on mapping a database that is not-quite-ORM friendly (it's
been a good test case so far). There's one unfortunate table that
doesn't have a primary key (and should really be multiple tables
anyhow). I think we'll ultimately fix the table, but I'm trying to see
what I can do while preserving compatibility with the existing code.
I have one table that's mapped nicely, but I need to do a left outer
join with that table that has no primary key (and therefore has no
mapper). I've managed to get the join mixed in with the query by
making the join between the two table objects, but the rest of the
select is still referring to the nicely mapped class. Here's a sketch:
t1 = Table("t1", metadata,
Column("id", Integer, primary_key=True),
Column("foo", String))
class T1(object):
pass
t1_mapper = mapper(T1, t1)
t2 = Table("t2", metadata,
Column("tid", Integer, ForeignKey("t1.id")),
Column("someval", Integer)
q = session.query(T1)
q.select(or_(and_(t2.c.someval >= 10, t2.c.someval <= 20),
t1.c.foo.like("%hi%")),
from_obj=[t1.outerjoin(t2, t1.c.id == t2.c.tid)])
This ends up generating a query with something like this:
SELECT ... FROM t1, t1 LEFT OUTER JOIN t2 ON t1.id = t2.tid ...
which generates errors, since t1 is selected twice.
The original query that I'm trying to reproduce, by the way, was like this:
"SELECT DISTINCT (t1.*) FROM t1 LEFT OUTER JOIN t2 "
"ON (t1.id = t2.tid) "
Most OR mappers would've thrown in the towel long ago... SQLAlchemy
gets pretty darn close, even with messy scenarios like this.
Any tips?
Kevin
--
Kevin Dangoor
TurboGears / Zesty News
email: [EMAIL PROTECTED]
company: http://www.BlazingThings.com
blog: http://www.BlueSkyOnMars.com
-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys -- and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
