Re: Pagers and Iterators

[prev] [thread] [next] [Date index for 2005/02/25]

On Fri, 25 Feb 2005 12:14:01 +0000, Simon Wistow <simon@xxxxxxxxxx.xxx> wrote:
> On Fri, Feb 25, 2005 at 06:55:30AM -0500, Sean Davis said:
> > It looks like the some_munging is done per-row?  If that is the case,
> > could you apply a trigger when you insert or update the table to
> > compute the value of some_munging(row) and store it in the database so
> > that you can later sort on it?
> 
> Not really. The munging is actually a live calculation. Sorry, I should
> really have been much clearer when I simplified the example.

It still seems like the database is the right place to do this work. 
Can you not write a function in your database that does the
calculations?

Select id, foo(col1, col2, ?, ?) as foo from tablename order by foo;

Not all databases will allow you to sort on the results of an
aggregate function, but it might be worth a look.

If you really have to do this in perl and Class::DBI, then you will
have to pull all rows out of the database, create objects from them,
then sort the objects, and grab the Page set that you need.  If you
have a lot of rows, then that is a lot of objects...

Or you could write a custom search method that pulled out the rows
manually, ran the sort, splice out only the set of rows that you need,
and then manually build the Class::DBI objects from them and returned
that set of objects (you can pass all the ids to _ids_to_objects to do
that).  That way you only have to create objects from the rows that
you are actually using.

Cheers,

        -- 
        Cees Hek