Hi, Thijs,
On Thu, 2013-04-04 at 21:36:56 +0200, Thijs Engels wrote:
> 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
>
> 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...
You were on correct path, what you're missing is the fact that result of
.subquery() is an object which can be used like a Table in another
query. So to access last_event in your stmt subquery, you would write
it like this:
stmt.c.last_event
Following is the declaration of two tables (using declarative extension)
and your full query:
class Session(Base):
__tablename__ = 'sessions'
session_id = Column(Integer, primary_key=True)
date = Column(DateTime())
class Event(Base):
__tablename__ = 'events'
session_id = Column(Integer, ForeignKey('sessions.session_id'),
primary_key=True)
event_id = Column(Integer, primary_key=True)
code = Column(Integer)
last_event = db_session.query(
Event.session_id,
func.max(Event.event_id).label('event_id')
).group_by(Event.session_id).subquery()
event = db_session.query(
Event.session_id,
Event.code
).join(last_event, and_(
Event.session_id == last_event.c.session_id,
Event.event_id == last_event.c.event_id
)).subquery()
q = db_session.query(
Session,
event.c.code
).outerjoin(event).order_by(Session.session_id)
--
Audrius Kažukauskas
http://neutrino.lt/
pgpIvTrDJMaJd.pgp
Description: PGP signature
