Re: [SQLObject] Can SQLObject do this?

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

Hello!

On Mon, Aug 18, 2008 at 04:13:35PM -0700, Golemon, Buck wrote:
> The most
> bothersome thing to me about ORM's is that they seem to only return data
> from one specific table at a time.

   Well, SQLObject is not better in this aspect - its main .select() method
only returns columns from one table. Well, the table could be actually
a virtual table - that is, a VIEW, but you have to create the VIEW in
advance and describe it in Python using SQLObject's language.

> q = db.select("FlowSubTarget", alias(max("FCassy"), "MAX"), "IsRelease")
> q.filters(
>         MetricType = 'i',
>         Project = 'mario',
>         Tapeout = 'sa11'
>     )
> q.add_filter(MaxMTime, ">", "DATE_SUB(NOW(), INTERVAL 10 DAY)")
> q.add_filter(FlowSubTarget, "RLIKE BINARY", "'^Sort.*(Stp|Setup)'")
> q.add_filter(FCassy, ">=", 0)
> q.group_by("FlowSubTarget", "IsRelease")
> print q

   SQLObject classes do not support GROUP BY and a list of columns from
different tables (both limitations are for one reason, actually). But there
is a lower level mechanism in SQLObject - called SQLBuilder - that can do
both. You query would be roughly translated to SQLBuilder's language like
this:

from sqlobject.sqlbuilder import *

columns = ("FlowSubTarget", func.max("FCassy"), "IsRelease")
q = []
q.append(Flow.q.id == Names.q.flow) # Do the join
q.append(SQLOp(MaxMTime, ">", "DATE_SUB(NOW(), INTERVAL 10 DAY)"))
q.append(SQLOp(FlowSubTarget, "RLIKE BINARY", "'^Sort.*(Stp|Setup)'"))
q.append(SQLOp(FCassy, ">=", 0))
query = AND(*q)

for row in connection.query(connection.sqlrepr(
   Select(columns, where=query, groupBy=("FlowSubTarget", "IsRelease"))
)):
   print row

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