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.sentm.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.sentm.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.sendmessages.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
-~--~~~~--~~--~--~---
