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
_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/[email protected]
