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

[Hernan Lopes]

This is how i have done pagination with ease in catalyst:

1. On the controller
my $results =3D $c->model('DB::Person')->search({});
$c->stash(
results =3D> $results,
pager =3D> $result->pager,
);

2. create a  pagination.tt and include on your views:
<!-- PAGINATION VIEW BEGIN -->
[% IF pager
%]

<center>
    <div class=3D"pagination-space">
      <div class=3D"span-7 height35 paddingTop5 paddingBottom5 quiet">
        Total <strong class=3D"loud">[% pager.last_page %]</strong> p=E1gin=
as,
<strong class=3D"loud">[% pager.total_entries %]</strong> itens.
      </div>
      <div class=3D"span-9 height35 paddingTop5 paddingBottom5 last">
       [% IF pager.previous_page %]
           <a href=3D"[% c.req.uri_with( page =3D> pager.first_page ) %]"
class=3D"noborder">&laquo; Primeira</a>
           <a href=3D"[% c.req.uri_with( page =3D> pager.previous_page ) %]"
class=3D"noborder">&lt; Anterior </a>
       [% END %]
        |
       [% IF pager.next_page %]
           <a href=3D"[% c.req.uri_with( page =3D> pager.next_page ) %]"
class=3D"noborder">Pr=F3xima &gt; </a>
           <a href=3D"[% c.req.uri_with( page =3D> pager.last_page ) %]"
class=3D"noborder">=DAltima &raquo; </a>
       [% END %]
      </div>


      <div class=3D"span-16 height35 paddingTop5 paddingBottom5">
      P=E1ginas.
[%#        P=E1gina pager.current_page %]

       [%
           start =3D (pager.current_page - 6) > 0               ?
(pager.current_page - 6) : 1;
           end   =3D (pager.current_page + 6) < pager.last_page ?
(pager.current_page + 6) : pager.last_page;
           FOREACH page IN [ start .. end  ]
       %]
           [% IF pager.current_page =3D=3D page %]
               <a class=3D"current"> [% page %] </a>
           [% ELSE %]
               <a href=3D"[% c.req.uri_with( page =3D> page ) %]">[% page %=
]</a>
           [% END %]
       [% END %]
      </div>

   </div>
</center>
[% END %]
<!-- PAGINATION VIEW END -->

--hernan

On Tue, Dec 14, 2010 at 11:21 PM, Matthew Braid <dbixclass at mdb.id.au> wrote:

> 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 =3D> 'value'}, {select =3D> ['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 =3D> 'value'}, {columns =3D> ['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/dbix-class@lists.scsys.co.uk
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20101214/5c5=
5affd/attachment.htm