Fantastic: and now i don't need the join to the persons table anymore - it stays hidden inside the message object, so i can just ask for "sender.name" or whatever i want - that's nice - If I have the objects, i can delegate the use of them to others, maybe leading to higher level of reuse (= less bugs) of the 'sql'.
Me on sqlalchemy: """ One of the things, I like about sql, is it's language independency - it can be discussed with almost all programmers and powerusers and even to some extent with laymen. With this set of tools I can (i hope;-) accomodate two things - take some of the tedious things away from expressing myself in sql but still have sql (kindly provided by sqlalchemy), that i can discuss with powerusers""" k = model.message_table.alias('k') m = model.message_table return dbsession.query(model.Message).\ filter(m.c.toid==79487281).\ 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() On Jun 19, 12:03 am, gardsted <[EMAIL PROTECTED]> wrote: > 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 -~----------~----~----~----~------~----~------~--~---