I was wondering whether someone would be able to help me out with a
SQLAlchemy query (not sure whether this is the correct term)
Assume a database with two tables; sessions and events. Sessions has a
primary key id and some additional information. Events has a primary key
which consists of session_id (foreign key to Sessions) and event_id
which is using a sequence. This table contains an additional column
which contains the value I am interested in.
SESSIONS
- SESSION_ID [PK]
- DATE
EVENTS
- SESSION_ID [PK + FK]
- EVENT_ID [PK]
- CODE
I assume the corresponding classes in Python/SQLAlchemy are self
explanitory...
Each session has multiple events, what I am interested in is the code of
the last event per session (event with highest event id).
I managed to come up with the SQL which will do this:
SELECT
SSN.SESSION_ID,
SSN.DATE
FROM
SESSIONS SSN
LEFT JOIN
(SELECT
EVT.SESSION_ID,
EVT.CODE
FROM
EVENTS EVT
INNER JOIN
(SELECT
SESSION_ID,
MAX(EVENT_ID) AS EVENT_ID
FROM
EVENTS
GROUP BY
SESSION_ID) LAST_EVENT
ON
AEL.SESSION_ID = LAST_EVENT.SESSION_ID AND
AEL.EVENT_ID = LAST_EVENT.EVENT_ID) EVENT
ON
EVENT.SESSION_ID = SSN.SESSION_ID
ORDER BY
SSN.SESSION_ID;
Hence initially getting the max event_id, which is then joined with the
events table to extract the code, which in the end is join with the
sessions table.
I tried to transform this query to SQLAlchemy, but am getting stuck.
Looking at the various examples as found in the documentation I gathered
that I should start with the creation of a sub-query:
stmt = oracle.query(Event.session_id,
func.max(Event.id).label("last_event")).group_by(Event.session_id).subquery()
But I can not figure out how to use this sub-query in the two remaining
joins...
Could anyone point me in the right directions?
Thijs
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
