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

[Matthew Braid]

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 *'], ....})

This did the right thing database-wise (DBIC_TRACE revealed exactly
the right SQL statement), but the resulting rows' accessors (like id,
name etc) all returned nothing - I ended up with a big table of no
data.

So I changed it to:

  $rs->search({column => 'value'}, {columns => ['SQL_CALC_FOUND_ROWS *'], ...})

But this resulted in an invalid SQL statement since 'me.' had been
prepended to 'SQL_CALC_FOUND_ROWS'.

Even if I had succeeded in getting the first part working, I have no
idea how to go about formulating the statement 'SELECT FOUND_ROWS()'
with DBIx::Class - there's no FROM table, and from what I can tell
DBIx::Class seems to assume there is always a table.

Is there a method for doing both of this statements? Falling back to
using the raw database handle $schema->storage->dbh seems to be a bit
of a shame here....

Thanks,
MDB