[Dbix-class] DBIx::Class, mysql and 'SQL_CALC_FOUND_ROWS'/'SELECT FOUND_ROWS()'

[Emmanuel OTTON]

Le 15 déc. 2010 à 02:21, Matthew Braid a écrit :

> Hi all,
> 
> I need to do pagination for a site I'm building in Catalyst, and my
> underlying RDMS is mysql. Rather than using count and search, I want
> to use mysql's SQL_CALC_FOUND_ROWS modifier and the 'SELECT
> FOUND_ROWS()' statement (since that's a lot faster than count+search).
> 
> Unfortunately I have no idea how to actually do this with DBIx::Class.
> 
> My initial guess for the first part was something along the lines of:
> 
>  $rs->search({column => 'value'}, {select => ['SQL_CALC_FOUND_ROWS *'], ....})

You should NOT use specific mysql dialect, DBIx::Class is built, among other advantages, to bring you portability, that means it includes a good solution for nearly everything you could need, including paginate resultsets.

You just have to add rows and page parameters to the resultset constructor , and your rs will include a pager object.

See the manual, for example this one:
http://search.cpan.org/~elliott/DBIx-Class-0.06002/lib/DBIx/Class/Manual/Cookbook.pod#Paged_results

If your RDBMS and the DBD::YourRdbms driver support it, DBIx::Class will even do "the right thing", i.e. issue the right SQL statements (for mysql: LIMIT and OFFSET) to fetch only the requested rows only, instead of fetching the whole table and paging through it. 

--
Emmanuel OTTON - Responsable informatique - Ecole des mines d'Albi-Carmaux - Tél: 05 63 49 30 86