I think you hit Class::DBI's major weakness. :(

Personally, i love Class::DBI, but when it comes to doing this stuff "professionally", i just stick with plain old DBI and selectall_arrayref or selectcol_arrayref and specify the optional Slice parameter. Observe:

my $sth = $dbh->selectall_arrayref('
   select id,title,year from movie limit 2
',{Slice => {}});

print Dumper $sth;

__END__
$VAR1 = [
          {
            'title' => 'Alien',
            'year' => '1979',
            'id' => '78748'
          },
          {
            'title' => 'Aliens',
            'year' => '1986',
            'id' => '90605'
          }
        ];
[download]

If you are wanting to further separate your SQL from your Perl, look into Class::Phrasebook::SQL

UPDATE: yup, i shoulda named it $movies

L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)

Just a nitpick, but $sth is practically a reserved word when writing DBI apps. I'd call it something else, like $results.

The invoking code looks like this:

   use Data::Page;
   my $total_rows = $table->get_count_sql($sql_where_clause);

   # 
   my $pager = Data::Page->new($total_rows,
                               $G::CF{rows_per_page}, 
                               $current_page_num);

   # Now get only the needed chunk from the DB
   my @results = $table->get_range_sql($sql_where_clause, 
                                       $pager->first,
                                       $pager->last);
[download]

The interesting bit was getting the SQL that would work with Oracle to provide a "limit" or an "offset" like MySQL. This ends up being ridiculously complicated:

Select
  *
From (
   SELECT
        %s
        ,rownum R
   FROM
        (SELECT *
        FROM %s
        %s
        )
   )
WHERE R BETWEEN %s AND %s
[download]

THIS RELEASE IS FOR TEMPORARY DEVELOPMENT. Hope this will eventually be merged into Class::DBI and/or Class::DBI::mysql.

Regrettably, the code notes that it the SQL it implements is specific to MySQL. In Oracle, for example, you use a "where rownum < ?" to limit results. Thus the code is not terribly portable (yet). It would be nice to see that added.

Cheers,
Ovid

New address of my CGI Course.

It might be worthwhile having a look at Class::DBI::Pager. It handles this sort of thing for you, although I have a feeling that it doesn't use LIMITs on the select statements. So if your queries really are massive, it won't solve the problem you are facing. It does however simplify getting ranges of data when using Class::DBI.

- Cees

I don't quite understand what you mean by "moving stuff between objects and the database" - I thought this requires generating an "UPDATE" SQL sentence.

That is what I'm talking about -- selecting things from the database, doing all the DBI busywork, moving it into objects, providing accessors on those objects, tracking if I change anything, saving it back to the database if I have, doing lazy-loading of groups of data, iterating over lists of primary keys and inflating them to objects if I try to use them, having a centralized place to do validation before saves, having a centralized place to do caching, etc. All of these things are done for me by Class::DBI. If I had to write every line of SQL, it would still be valuable to me.

For more complex queries you need to fall back on the underlying Ima::DBI query mechanism.

       Music::Artist->set_sql(most_cds => qq{
                SELECT artist.id, COUNT(cd.id) AS cds
                  FROM artist, cd
                 WHERE artist.id = cd.artist
                 GROUP BY artist.id
                 ORDER BY cds DESC
                 LIMIT 10
        });

        my @artists = Music::Artist->search_most_cds();
[download]

jdtoronto