I am inexperienced in SQL / SQLAlchemy, and am trying to write a SQLAlchemy query from a table called Event with data such as this:
I would like to select the rows with Type equal to "Viewed Tab", but only if there does not exists a row with the same Session value that has a Type equal to "Mobile View". So in the sample data above I would want the query to return the rows with EventId equal to 150 and 154, but not 147.
Thank you.
Assuming the table is defined as per below:
class Event(Base):
__tablename__ = 'events'
EventId = Column(Integer, primary_key=True)
Session = Column(Integer)
Type = Column(String)
the query to produce the desired result can be written as:
viewed = aliased(Event, name='viewed')
mobile = aliased(Event, name='mobile')
qry = (session.query(viewed)
.filter(viewed.Type == 'Viewed Tab')
.outerjoin(mobile, and_(
viewed.Session == mobile.Session,
mobile.Type == 'Mobile View')
)
.filter(mobile.Session == None)
)
This will produce a query without any aggregations:
SELECT viewed."EventId" AS "viewed_EventId",
viewed."Session" AS "viewed_Session",
viewed."Type" AS "viewed_Type"
FROM events AS viewed
LEFT OUTER JOIN
events AS mobile
ON viewed."Session" = mobile."Session"
AND mobile."Type" = 'Mobile View'
WHERE viewed."Type" = 'Viewed Tab'
AND mobile."Session" IS NULL
Try:
select e.*
from event e
join (select eventid, session
from event
group by eventid, session
having sum(case when type = 'Viewed Tab' then 1 else 0 end) > 0
and sum(case when type = 'Mobile View' then 1 else 0 end) = 0) v
on e.eventid = v.eventid
I'm not sure what syntax limitations there are with sql alchemy, however.
