How to implement a null coalescing statement in SQLAlchemy?

Question

Or how can I make this work 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 the 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, of course, all the typical installation codes.

Now when I try to do session.query(Task).filter(Task.hours_spent > 3).all()

I get NotImplementedError: <built-in function getitem> from the string sum(i.hours_spent...

So, I looked at this part of the documentation and suggested that there might be some way so that I can write something that will do what I want. This part also looks as if it could be useful, and I will look at it, waiting for an answer here;)

Answer 1

SQLAlchemy is not smart enough to build a tree of SQL expressions from these operands, you need to use the explicit constructor propname.expression to provide it. But then another problem arises: there is no portable way to convert the interval to hours in the database. You would use TIMEDIFF in MySQL, EXTRACT(EPOCH FROM ... ) / 3600 in PostgreSQL, etc. I suggest changing properties to return timedelta instead and compare 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')) 

Final request:

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

which translates to (on the 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 

Answer 2

For a simple example of the SQLAlchemy merge function, this can help: Handling null values ​​in a SQLAlchemy query is the equivalent of isnull, nullif, or coalesce .

Here are a couple of key lines of code from this post:

 from sqlalchemy.sql.functions import coalesce my_config = session.query(Config).order_by(coalesce(Config.last_processed_at, datetime.date.min)).first()