I think you want the final SQL query to look something like this:
select * from
card_table
join repetition_table on repetition_table.card_key = card_table.id
where repetition_table.rep_number >= 1 and repetition_table.rep_number <= 4 and
card_table.id in
(select ct2.id from card_table AS ct2
join repetition_table AS rt2 on rt2.card_key = ct2.id
AND <YOUR CUSTOM CONDITIONS HERE>)
The main difference is that I'm doing a join between card and repetition on
both the outer and inner queries. This lets me constrain the join and avoid
getting the Cartesian product.
Barry
----- Original Message ----
From: pbienst <[EMAIL PROTECTED]>
To: sqlalchemy <sqlalchemy@googlegroups.com>
Sent: Thursday, October 18, 2007 7:48:36 AM
Subject: [sqlalchemy] Advice on complicated (?) SQL query
Hi,
I have a query which works fine through the ORM, albeit a bit slow, so
I want to drop down to the SQL layer to speed things up. However,
being an SQL novice, I can't seem to figure out how to get it to work.
I have a database containing the history of repetitions with a flash
card program. I have a table containing the flash cards, and a second
table containing the details of all the repetitions for all the
flashcards. I want to look at all cards where repetition 5 obeys
certain criteria, and then for these cards look at repetitions 1 to 4.
Using the ORM, that's easy enough:
repetitions =
db_session.query(Repetition).filter((Repetition.rep_number==5) \
&
(Repetition.grade==2))
for r in repetitions:
for index in range(4):
print index, r.card.repetitions[index].grade
After browsing through the documentation, I have the impression that
using aliases could be the way to go. I tried the following code
(which would only give me repetition 1 instead of 1 to 4, but a loop
is easily made):
repetition_table_2 = repetition_table.alias()
s = select([repetition_table.c.grade],
(repetition_table.c.rep_number==1) & \
(repetition_table.c.card_key==repetition_table_2.c.card_key) & \
(repetition_table_2.c.rep_number==5) & \
(repetition_table_2.c.grade==2))
However, this runs forever, so I'm guessing it escapes into the
wonderful world of Cartesian products.
Can anybody help me contruct the proper SQL query or otherwise speed
up the ORM version?
Thanks!
Peter
____________________________________________________________________________________
Take the Internet to Go: Yahoo!Go puts the Internet in your pocket: mail, news,
photos & more.
http://mobile.yahoo.com/go?refer=1GNXIC
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
