[sqlalchemy] Joined Query

[Heston James - Cold Beans]

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