[storm] SQL to Storm

[James Henstridge]

On Mon, Sep 14, 2009 at 7:16 PM, Eduardo Willians<edujurista at gmail.com> wrote:
> Did I say something wrong or stupid?

Nope. No one got round to answering the question though :(  I'll
rectify that now.

>
> 2009/9/9 Eduardo Willians <edujurista at gmail.com>:
>> Is there a stormic way to execute the following SQL statement?
>>
>> store.execute("""SELECT MAX(id) FROM gear.op WHERE CAST(id AS
>> character) LIKE '9%';""").get_one()
>>
>> I'm using postgres.

There isn't anything in Storm to generate the WHERE clause you've used
here.  However, you can insert arbitrary SQL into an expression using
the SQL() operator.  Something like this:

    result = store.find(GearOp, SQL("CAST(gear.op.id AS character) LIKE '9%'"))
    maximum = result.max(GearOp.id)

You can combine SQL() with other Storm expression objects in pretty
much any situation.  The one downside is that they won't give you the
"auto tables" behaviour where Storm guesses what tables need to be
used for a given query.  It wasn't required in the above, because the
table used in the SQL() expression is already referenced.

If you want to keep the "auto tables" behaviour in other cases, you
can manually specify what tables the expression uses.  Something like:

    SQL("...", tables=[GearOp])

I hope this helps,

James.