throwing from_obj into the query.select() is going to make kind of a
mess, since query.select() is already building up its own select
statement thats going to conflict.
the real answer is to decide what t1 and t2 logically represent.
just because t2 doesnt have a primary key defined in the database,
doesnt mean SA cant consider one of its columns to be "primary". you
can map to the exact select statement you want and just explicitly
tell the mapper what columns comprise the "primary key" using the
"primary key" argument.
> 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) "
this query doesnt quite make sense ? how is this different from just
saying "select * from t1" (since t2's columns are not in the column
clause, youre doing "distinct" on them so multiple assocaitions to t2
get munged into one, and youre doing LEFT OUTER JOIN so the rows in
t1 dont even have to correspond to t2) ?
On Jul 25, 2006, at 12:41 PM, Kevin Dangoor wrote:
> 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
-------------------------------------------------------------------------
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
