How to define a hybrid expression?

Question

I have two classes, Event and Booking. One Event can hold many bookings.

class Event(Base):
    __tablename__ = 'event'
    id = Column(Integer, primary_key=True)
    space = Column(Integer)

class Booking(Base):
    __tablename_ = 'booking'
    id = Column(Integer, primary_key=True)
    event_id = Column(Integer, ForeignKey('event.id'))
    event = relationship('Event', backref=backref('bookings'))

Now I want to calculate the occupancy and decided to go for a hybrid property of the Event class, like this:

@hybrid_property
def occupancy(self):
    return float(len(self.bookings)) / float(self.space)

This works fine in some cases, but when I want to be able to filter on occupancy, like this:

session.query(Event).filter(Event.occupany > 0.5)

Doing that I get this error:

TypeError: object of type 'InstrumentedAttribute' has no len()

So I realized I probably need a hybrid expression and came up with:

@occupancy.expression
def occupancy(cls):
    return func.count(select([Booking.id]).where(cls.id == Booking.event_id).label('occupancy')) / cls.space

This however results in a MySQL error:

sqlalchemy.exc.ProgrammingError: (_mysql_exceptions.ProgrammingError) 
(1111, 'Invalid use of group function') [SQL: u'SELECT event.id AS 
event_id FROM event \nWHERE count((SELECT booking.id \nFROM booking 
\nWHERE event.id = booking.event_id)) / event.space > %s'] 
[parameters: (0.5,)]

How can I construct the hybrid expression? Or is there a better way of achieving what I want?

Answer 1

The counting should be done inside the scalar subquery. The current query is trying to apply the count() function to the result set of the subquery in the WHERE clause of the enclosing query, which on the other hand has no grouping, and so you get the error – not to mention that aggregate functions as predicates belong in a HAVING clause. Just move the call:

@occupancy.expression
def occupancy(cls):
    stmt = select([func.count(Booking.id)]).\
        where(cls.id == Booking.event_id).\
        label('occupancy')
    return stmt / cls.space