Sorting time column derived from values of other columns using hybrid_property

Question

I'm trying to build an event-booking system as a side project to learn python and web development. Below are two of the models implemented in my project. An EventSlot represents a timeslot scheduled for a particular Event.

Models

from app import db
from sqlalchemy import ForeignKey
from dateutil.parser import parse
from datetime import timedelta
from sqlalchemy.ext.hybrid import hybrid_property


class Event(db.Model):
    event_id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String, index=True, nullable=False)
    duration = db.Column(db.Float, nullable=False)
    price = db.Column(db.Float, nullable=False)

    slots = db.relationship('EventSlot', cascade='all, delete', back_populates='event')


class EventSlot(db.Model):
    slot_id = db.Column(db.Integer, primary_key=True)
    event_date = db.Column(db.DateTime, nullable=False)
    event_id = db.Column(db.Integer, ForeignKey('event.event_id'))

    event = db.relationship('Event', back_populates='slots')

I've provided an admin page (Flask-Admin) for admin-users to view database records. On the EventSlot page, I included 'Start Time' and 'End Time' column which I want to make sortable. I've appended to the EventSlot model the following:

class EventSlot(db.Model):
    #...

    ## working as intended ##
    @hybrid_property
    def start_time(self):
        dt = parse(str(self.event_date))
        return dt.time().strftime('%I:%M %p')

    @start_time.expression
    def start_time(cls):
        return db.func.time(cls.event_date)


    ## DOES NOT WORK: can display derived time, but sorting is incorrect ##
    @hybrid_property
    def end_time(self):
        rec = Event.query.filter(Event.event_id == self.event_id).first()
        duration = rec.duration * 60
        derived_time = self.event_date + timedelta(minutes=duration)
        dt = parse(str(derived_time))
        return dt.time().strftime('%I:%M %p')

    @end_time.expression
    def end_time(cls):
        rec = Event.query.filter(Event.event_id == cls.event_id).first()
        duration = '+' + str(int(rec.duration * 60)) + ' minutes'
        return db.func.time(cls.event_date, duration)

As can be seen from the image below, the sort order is wrong when I sort by 'end time'. It appears to be still sorting by start time. What might be the problem here?

(Admittedly, I still don't understand hybrid_properties. I thought I had got it when got start_time working, but now it seems I still don't understand a thing...)

Answer 1

In the expression for end_time the cls.event_id represents a column, not a value, so the query ends up performing an implicit join between Event and EventSlot and picks the first result of that join. This of course is not what you want, but instead for an EventSlot you want to find out the duration of the related Event in SQL. This seems like a good place to use a correlated scalar subquery:

@end_time.expression
def end_time(cls):
    # Get the duration of the related Event
    ev_duration = Event.query.\
        with_entities(Event.duration * 60).\
        filter(Event.event_id == cls.event_id).\
        as_scalar()
    # This will form a string concatenation SQL expression, binding the strings as
    # parameters to the query.
    duration = '+' + ev_duration.cast(db.String) + ' minutes'
    return db.func.time(cls.event_date, duration)

Note that the query is not run when the attribute is accessed in query context, but becomes a part of the parent query.