How do I implement a null coalescing operator in SQLAlchemy?

null-coalescing-operator, python, sqlalchemy

Solution

SQLAlchemy is not smart enough to build SQL expression tree from these operands, you have to use explicit `propname.expression` decorator to provide it. But then comes another problem: there is no portable way to convert interval to hours in-database. You'd use `TIMEDIFF` in MySQL, `EXTRACT(EPOCH FROM ... ) / 3600` in PostgreSQL etc. I suggest changing properties to return `timedelta` instead, and comparing apples to apples.

from sqlalchemy import select, func


class Interval(Base):
    ...

    @hybrid_property
    def time_spent(self):
        return (self.end or datetime.now()) - self.start

    @time_spent.expression
    def time_spent(cls):
        return func.coalesce(cls.end, func.current_timestamp()) - cls.start


class Task(Base):
    ...

    @hybrid_property
    def time_spent(self):
        return sum((i.time_spent for i in self.intervals), timedelta(0))

    @time_spent.expression
    def hours_spent(cls):
        return (select([func.sum(Interval.time_spent)])
            .where(cls.id==Interval.task_id)
            .label('time_spent'))

The final query is:

session.query(Task).filter(Task.time_spent > timedelta(hours=3)).all()

which translates to (on PostgreSQL backend):

SELECT task.id AS task_id, task.title AS task_title 
FROM task 
WHERE (SELECT sum(coalesce(interval."end", CURRENT_TIMESTAMP) - interval.start) AS sum_1 
FROM interval 
WHERE task.id = interval.task_id) > %(param_1)s

Problem

Or how do I make this thing work? I have an Interval object: ``` class Interval(Base): __tablename__ = 'intervals' id = Column(Integer, primary_key=True) start = Column(DateTime) end = Column(DateTime, nullable=True) task_id = Column(Integer, ForeignKey('tasks.id')) @hybrid_property #used to just be @property def hours_spent(self): end = self.end or datetime.datetime.now() return (end-start).total_seconds()/60/60 ``` And a Task: ``` class Task(Base): __tablename__ = 'tasks' id = Column(Integer, primary_key=True) title = Column(String) intervals = relationship("Interval", backref="task") @hybrid_property # Also used to be just @property def hours_spent(self): return sum(i.hours_spent for i in self.intervals) ``` Add all the typical setup code, of course. Now when I try to do `session.query(Task).filter(Task.hours_spent > 3).all()` I get `NotImplementedError: <built-in function getitem>` from the `sum(i.hours_spent...` line. So I was looking at this part of the documentation and theorized that there might be some way that I can write something that will do what I want. This part also looks like it may be of use, and I'll be looking at it while waiting for an answer here ;)

Original source