Thank You very much
Worked like a charm - only better - now I get objects.
I will now see, if I can understand what goes on ;-)
The results were the same rows.
def play5():
print ############### play5 #################
k = model.message_table.alias('k')
s = model.person_table
m = model.message_table
result=dbsession.query(model.Message).select_from(
m.join(s,m.c.fromid==s.c.personid)).\
filter(m.c.toid==123456).\
filter(~exists([1], and_(
k.c.toid==m.c.fromid,
k.c.fromid==m.c.toid,
k.c.sent>m.c.sent))
).\
order_by(desc(m.c.sent)).list()
for i in result:
print i
On Jun 18, 11:27 pm, Michael Bayer <[EMAIL PROTECTED]> wrote:
> On Jun 18, 2007, at 4:52 PM, gardsted wrote:
>
>
>
>
>
> > Dear List.
> > How do I do this more ormish?
>
> > The statement is supposed to find the latest messages which havent yet
> > been answered
> > by 123456, assuming a later message is an answer;-)
>
> > def play4():
> > print ############### play4 #################
> > engine=create_engine(dburi)
> > result=engine.execute("""
> > select m.fromid, s.name, m.sent from person s,message m
> > where toid=123456 and
> > s.personid = m.fromid and not exists (
> > select 1 from message k where
> > k.toid=m.fromid and k.fromid=m.toid
> > and k.sent > m.sent
> > )
> > order by m.sent desc
> > """)
> > ...
>
> > I completely fail to grasp how I get from the straight sql-
> > representation to the part where i
> > can actually benefit from the orm
>
> > Here am I: I have created a mapper where each message knows it's
> > sender and receiver by foreign key and attribute like this (and this I
> > like very much):
> > message_mapper=mapper(
> > Message,
> > message_table,
> > properties={
> > "sender": relation(Person,
> > primaryjoin=(message_table.c.fromid==Person.c.personid)),
> > "receiver": relation(Person,
> > primaryjoin=(message_table.c.toid==Person.c.personid))
> > }
> > )
>
> here are some rough approaches.
>
> text:
>
> session.query(Message).select_text("""select m.* from person s,
> message m
> where toid=123456 and
> s.personid = m.fromid and not exists (
> select 1 from message k where
> k.toid=m.fromid and k.fromid=m.toid
> and k.sent > m.sent
> )
> order by m.sent desc""")
>
> hybrid:
>
> session.query(Message).select_from(messages.join(person)).filter
> ("""toid=123456 and not exists (
> select 1 from message k where
> k.toid=messages.fromid and k.fromid=messages.toid
> and k.sent > messages.sent
> )""").order_by(desc(Message.c.sent)).list()
>
> fully constructed:
>
> k = messages.alias('k')
> session.query(Message).select_from(messages.join(person)).filter
> (messages.c.toid==123456).\
> filter(~exists([1], and_(k.c.toid==messages.c.fromid,
> k.c.fromid==messages.c.toid, k.c.send>messages.c.sent))).\
> order_by(desc(messages.c.sent)).list()
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
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
-~----------~----~----~----~------~----~------~--~---
