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

[Hernan Lopes]

i think i missed the rows and page

my $results =3D $c->model('DB::Person')->search({}, {
       rows =3D>
10,

        page =3D> $c->req->params->{page}||1,
});

On Tue, Dec 14, 2010 at 11:37 PM, Hernan Lopes <hernanlopes at gmail.com>wrote:

> 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=E1g=
inas,
> <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>wrot=
e:
>
>> 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/20101215/246=
cc8c0/attachment-0001.htm