I was able to get this working by dropping down to the SQL layer and
then using instances() to populate my objects.
That's a good suggestion about setting the "primary key" in the
mapper. I might be able to get something like that to work. Though, I
do think that we'll ultimately fix this table (which should really be
4 separate tables...)
Kevin
On 7/25/06, Michael Bayer <[EMAIL PROTECTED]> wrote:
> 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
>
>
--
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
