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

[Matthew Braid]

Hi,

While this is a nifty shortcut for calculating/displaying pagination
information, it does still result in the use of a count query on top
of a select query. I'm trying to avoid that entirely (it would be nice
to still have the same interface though, but I can just use Data::Page
directly in a pinch).

MDB

On Wed, Dec 15, 2010 at 12:01 PM, Hernan Lopes <hernanlopes at gmail.com> wrote:
> i think i missed the rows and page
>
> my $results = $c->model('DB::Person')->search({}, {
>        rows =>
> 10,
>         page => $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 = $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 <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 => '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/dbix-class@lists.scsys.co.uk
>>
>
>
> _______________________________________________
> 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
>