Re: search() without search criteria

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

Lasy workaround : 

$class->search({}, {rows => 10});

SELECT <columns> FROM <table> WHERE 1=1 works

Daniel

On Friday 29 April 2005 15:18, Matt S Trout wrote:
> 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:
> >
> > 
> >
> > 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 } ...)

        -- 
        Met vriendelijke groet, 

itXL 
Daniel Wijnands
daniel@xxxx.xx