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

Tue, 14 Dec 2010 17:41:16 -0800 

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

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

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

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


      <div class="span-16 height35 paddingTop5 paddingBottom5">
      Páginas.
[%#        Página pager.current_page %]

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

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

--hernan

On Tue, Dec 14, 2010 at 11:21 PM, Matthew Braid <[email protected]> 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 => '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]
>

_______________________________________________
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]

Reply via email to