Re: search() without search criteria

[prev] [thread] [next] [Date index for 2005/04/29]

On Fri, Apr 29, 2005 at 01:36:59PM +0100, Will Hawes wrote:
> I need to implement paging for potentially large result sets using CDBI. The pager should be usable for any CDBI subclass, so column names will not be known beforehand. Search criteria are optional. In order to keep things as efficient as possible I would like to make sure that only the number of records required for a single page will be returned by the query, so I would like to use search() and pass attributes e.g. "rows", "offset", etc as the second argument.
> 
> This works fine when search criteria are specified:
> 
> $class->search(column1 => value, {rows => 10});
> 
> When there are no search criteria specified however:
> 
> $class->search({}, {rows => 10});
> 
> search() tries to execute a query like this:
> 
> "SELECT <columns> FROM <table> WHERE"
> 
> which fails, because there is nothing after WHERE.
> 
> I'm aware there are probably workarounds - using retrieve_all() to return an iterator is one that springs to mind, a custom constructor is another - but I want to avoid returning whole result sets into memory or, worse, writing custom SQL.
> 
> I'd like to hear opinions regarding a patch to Class::DBI so that search() accepts an empty hash as the first argument. This would allow order_by, limit, etc to be applied to the whole result set rather than having to use search criteria too.

Have a look at chansen's http://search.cpan.org/dist/Catalyst-Model-CDBI-Sweet/

Next release will apparently remove the Catalyst dependency, and add a
retrieve_all with order and paging. In the meantime, you can do a search()
without conditions by defining a column 1 and doing search({ 1 => 1 } ...)

        -- 
            Matt S Trout            Brag sheet:    http://trout.me.uk/services.html
LAMP, Infrastructure        Contact:       services@xxxxx.xx.xx
   and Automation
     specialist                                       Do it once. Do it right.