Afternoon Chaps,
I've got a query here which I've been looking to reconstruct from the
standard SQL into a SQLAlchemy statement which will return a list of objects
but I'm really struggling to make any headway on it, I'm hoping you'll be
able to offer me some help.
I have two objects in my application, 'event' and 'message' and they have a
M21 relationship defined between them in the event class using declarative.
Below is the standard SQL for the query in question:
SELECT message.message_id
FROM event
INNER JOIN message ON event.message_id = message.message_id
WHERE CURRENT_DATE >= Date_Started
AND CURRENT_DATE <= COALESCE(date_ended,CURRENT_DATE)
AND (Is_All_Day = 1
OR CURRENT_TIME BETWEEN Time_Started AND Time_Ended)
AND (
Repeat_Type = 0
OR Repeat_Type = 1
OR Repeat_Type = 2
AND MOD( TO_DAYS(CURRENT_DATE)
- TO_DAYS(Date_Started),7) = 0
OR Repeat_Type = 3
AND MOD( TO_DAYS(CURRENT_DATE)
- TO_DAYS(Date_Started),14) = 0
OR Repeat_Type = 4
AND DAYOFMONTH(CURRENT_DATE)
= DAYOFMONTH(Date_Started)
OR Repeat_Type = 5
AND DAYOFYEAR(CURRENT_DATE)
= DAYOFYEAR(Date_Started)
OR Repeat_Type = 6
AND DAYOFWEEK(CURRENT_DATE) IN (2,3,4,5,6)
OR Repeat_Type = 7
AND DAYOFWEEK(CURRENT_DATE) IN (1,7)
)
AND NOT EXISTS
(SELECT 1 FROM event_exempt
WHERE event_id = event.event_ID
AND event_date = CURRENT_DATE)
The standard SQL there just returns the message.message_id, but obviously,
now we're using an ORM I'm looking to adapt this so that it returns a list
of 'message' objects to me.
Can anyone offer some sound advice and help on this? I've been playing
around with this using query().join() but keep hitting up against all kinds
of brick walls as this is all so new to me.
Thanks guys, I really appreciate the help you've been putting forward the
past few days.
Heston
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
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
-~----------~----~----~----~------~----~------~--~---
