sqlobject-discuss

[SQLObject] Tricky select with computed value

[Iwan V. <iw...@re...>]

Hi there,

We have a class similar to this definition:

class Article(SQLObject):
  publishedDate = DateTimeCol()
  runningTime = IntCol()   # This is the number of days the article 
                           #  runs after being published


To now query for Articles that are still within their running time, you
can do something like this (assuming Postgresql):

Article.select("(age(article.published_date)) < (article.running_time *
'1 day'::interval)")

Not very pretty, and in our real app, we cannot do this anyways for
unrelated reasons. 

Is there a way to express this in Python instead?

Or how else could one do it? (I wondered if you can add a computed
attribute on the class, and somehow have SQLBuilder understand that...
but I imagine that's not possible.)

In the above, age is actually a postgresql function, so that part you
can write using the "q magic" in python.  But the multiplication (and
the typecast) on the other end is problematic to write in Python.

-i

Re: [SQLObject] Tricky select with computed value

[Oleg B. <ph...@ph...>]

On Mon, Feb 02, 2009 at 03:22:05PM +0200, Iwan Vosloo wrote:
> Article.select("(age(article.published_date)) < (article.running_time *
> '1 day'::interval)")

   Try

from sqlobject.sqlbuilder import func, SQLConstant
Article.select(func.age(Article.q.publishedDate) < Article.q.runningTime * SQLConst("'1 day'::interval"))

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] Tricky select with computed value

[Iwan V. <iw...@re...>]

On Mon, 2009-02-02 at 17:12 +0300, Oleg Broytmann wrote:
> On Mon, Feb 02, 2009 at 03:22:05PM +0200, Iwan Vosloo wrote:
> > Article.select("(age(article.published_date)) < (article.running_time *
> > '1 day'::interval)")
> 
>    Try
> 
> from sqlobject.sqlbuilder import func, SQLConstant
> Article.select(func.age(Article.q.publishedDate) < Article.q.runningTime * SQLConst("'1 day'::interval"))


Great, thanks Oleg, that works.

-i