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
-~----------~----~----~----~------~----~------~--~---
