[jifty-devel] Frustration with limit's limitations

[Sterling Hanenkamp]

On Thu, Jun 26, 2008 at 4:08 PM, Ruslan Zakirov <ruz at bestpractical.com>
wrote:

> 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");


Thank you. I've been trying to understand the use of subclauses and
open_paren/close_paren, but was at loss. I'll put that into the docs. I do
note, however, that open_paren/close_paren have a "don't do this, we'll
change this as soon as we figure out how" note on it.


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


No, it is not ideal, at least not as long as we can't get anyone to agree on
what SQL is. However, if you're writing a quick and dirty app, SQL is an
excellent shortcut. I don't always need the ideal solution, but one that is
simple and works.


>
>
> 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.


I would love to see this incorporated into the trunk. But until then...

Cheers,
Sterling


>
>
>
>
> 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.
> _______________________________________________
> jifty-devel mailing list
> jifty-devel at lists.jifty.org
> http://lists.jifty.org/cgi-bin/mailman/listinfo/jifty-devel
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.jifty.org/pipermail/jifty-devel/attachments/20080626/7d80ed8d/attachment.htm