Re: search() without search criteria

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

On Friday 29 April 2005 13:36, 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.

How about CDBI::Plugin::Pager ? It also has a retrieve_all, which internally 
just sets the where clause to { 1 => 1 }. And it only retrieves records for 
the current page.

d. 



>
> 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.
>
> All feedback gratefully received.
>
> Regards
>
> WH