[jifty-devel] Frustration with limit's limitations

[Ruslan Zakirov]

subclauses is the way to deal with that and open_paren/close_paren.

something like:
$col->limit( column => 'parent', value => 12 );
$col->limit( column => 'task_type', value => 'action' );
$col->open_paren("my_clause");
$col->limit( subclause => "my_clause", column => 'status', value => 'open' );
$col->open_paren("my_clause");
$col->limit( subclause => "my_clause", column => 'status', value =>
'done', entry_aggregator => 'OR' );
$col->limit( subclause => "my_clause", column => 'completed_on',
operator => '>=', value => '2008-06-26 11:39:22' );
$col->close_paren("my_clause");
$col->close_paren("my_clause");

May be it's not ideal, but pure SQL is not a good too.

With tisql you can do:

$col->tisql( "parent = 12 AND task_type = 'action' AND (status =
'open' OR (status = 'done' AND completed_on >= '2008-06-26
11:39:22'))" );

But I found some design problems with tisql and have to rethink
language to address issues with one-to-many relations.



On Thu, Jun 26, 2008 at 9:23 PM, Sterling Hanenkamp
<sterling at hanenkamp.com> wrote:
> Every so often I want to defenestrate Jifty::DBI for the simple fact that I
> cannot describe a basic WHERE clause with it. For example, the query:
>
> SELECT *
> FROM tasks
> WHERE parent = 12 AND task_type = 'action' AND (status = 'open' OR (status =
> 'done' AND completed_on >= '2008-06-26 11:39:22'))
>
> Is there a way to do this? I haven't found a combination of limits and
> subclauses that will let me do this that works. The code within limit() is
> pretty complicated, so I haven't been able to unravel what it is doing, let
> alone how this is supposed to work. I'll write up a complex query example in
> the documentation if someone will give me a demonstration on how this is
> supposed to be done. I have not found a good example of a complex query
> either.
>
> My other issue with limit is that the format used in limit() is pretty
> verbose. Has there been any consideration for allowing arbitrary SQL to be
> used. I.e., this is arguably more straitforward:
>
> $tasks->limit(sql => "completed_on >= '2008-06-26 11:39:22'");
>
> than:
>
> $tasks->limit(column => 'completed_on', operator => '>=', value =>
> '2008-06-26 11:39:22');
>
> Or the use of a more flexible and compact format for limits, such as is used
> by SQL::Abstract:
>
> $tasks->limit(abstract => [ completed_on => [ '>=', '2008-06-26 11:39:22' ]
> ]);
>
> Cheers,
> Sterling
>
> _______________________________________________
> jifty-devel mailing list
> jifty-devel at lists.jifty.org
> http://lists.jifty.org/cgi-bin/mailman/listinfo/jifty-devel
>
>



-- 
Best regards, Ruslan.