Re: [sqlalchemy] right way to do ::interval queries with orm?

[Audrius Kažukauskas]

On Thu, 2012-11-08 at 10:06:54 +0000, Chris Withers wrote:
> What's the "right" way to do a query such as the following using the
> orm layer?
> 
> select * from event where date < now() - '3 years'::interval

Here's how I do it in one of my projects:

  from sqlalchemy import func
  from sqlalchemy.sql.expression import FunctionElement
  from sqlalchemy.ext.compiler import compiles

  class subtract_interval(FunctionElement):
      type = Date()
      name = 'subtract_interval'

  @compiles(subtract_interval)
  def compile_subtract_interval(element, compiler, **kwargs):
      return '(%s::date - %s::interval)' % (
          compiler.process(element.clauses.clauses[0]),
          compiler.process(element.clauses.clauses[1]),
      )

  res = session.query(Foo).filter(
      Foo.date < subtract_interval(func.now(), '3 years')
  ).all()

I have borrowed the technique from an example that appeared on this same
mailing list in the past.

-- 
Audrius Kažukauskas
http://neutrino.lt/

pgpzeyUapHmVy.pgp
Description: PGP signature