Thank you very much for your effort in putting together a complete and
working example.
Much appreciated,
Thijs
On Sat, Apr 6, 2013, at 18:10, Audrius Kažukauskas wrote:
> 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/
> Email had 1 attachment:
> + Attachment2
> 1k (application/pgp-signature)
--
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.
