FuelPHP Forums

h3x

Hi,

This is my (not working) pagination setup:

$config = array(

'pagination_url' => Uri::create('scores'),

'total_items' => 1,

'per_page' => 20,

'uri_segment' => 3,

);

$pagination = Pagination::forge('scores', $config);

$get = OT::scores((int)$page * $pagination->per_page, $pagination->per_page);

I wonder how can I get the "total_items", when the query ($get) has not run yet... This means I'd have to double my query adding the `COUNT(*)` function and removing the limits on it... this seems pointless, really. My query is quite large and I'm caching it in order to save the execution time.

There should be some possibilities to set the total_items AFTER the pagination forge and then it should rebuild the pagination results.

Any suggestions?

Regards

Harro

You need to run two queries, one for the total count, one for the selection of data for the page. No way to do it in one go.

So you get something like:

        // make sure $page contains something useful
        (int) $page < 1 and $page = 1;

        // construct the base query
        $query = Model\Invoice::query()
            ->where('invoice', 'LIKE', '%'.$search.'%')
            ->or_where('name', 'LIKE', '%'.$search.'%')
            ->or_where('address', 'LIKE', '%'.$search.'%')
            ->or_where('city', 'LIKE', '%'.$search.'%')
            ->or_where('contact', 'LIKE', '%'.$search.'%');

        // set pagination information
        \Pagination::instance()->uri_segment = 4;
        \Pagination::instance()->per_page = \Config::get('application.pagination', 20);
        \Pagination::instance()->total_items = $query->count();
        \Pagination::instance()->pagination_url = \Uri::create('finance/invoices/search');

        // content for this action
        \Theme::instance()->set_partial('content', 'invoices/index')
            ->set('invoices', $query->order_by('name', 'ASC')
                    ->rows_offset(\Pagination::instance()->offset)
                    ->rows_limit(\Pagination::instance()->per_page)
                    ->get());

If you cache the results, you should also cache the totals, otherwise your data is inconsistent. There is no way to calculate totals from a cached subset of data.

h3x

My query is not that simple though...

$query = DB::query("

SELECT SQL_CALC_FOUND_ROWS `p`.*, `pi`.`emp`

FROM " . DB::FDB('users') . ".`user` `p`

LEFT JOIN " . DB::FDB('users') . ".`index` `pi` ON (`pi`.`id` = `p`.`aid`)

LEFT JOIN " . DB::FTABLE('account') . ".account ON (p.aid= account.id)

WHERE

p.point > 100

AND p.login NOT LIKE '%<%'

AND p.name NOT IN (SELECT sname FROM misc.staff)

AND account.state = 'FINE'

ORDER BY p.point DESC, p.dev DESC

LIMIT " . (int)$limit . "

OFFSET " . (int)$offset);

I run this query on the table containing more than 500k rows...

Harro

Doesn't matter, you still need to run it once without the LIMIT and OFFSET to get the total number of rows in the result, which is required to calculate the number of pages.

With 500K rows, how pointless is this in a paging context? How many pages does that produce? And which user is going to page through them all?

To work around that, you would need something like open-ended pagination, where there is no "last page" defined, but you check, when you request the next page, if you have any results and if not, display a message that you are on the last page. Problem is when you only have 10 pages, and a user requests page 100. It's a bit resource heavy to start requesting pages from 99 down until you get a hit...

h3x

Indeed I wont paginate the whole 500K rows because that would be really pointless, the table itself contains 500K rows, but there are some conditions in the query so the final output would be smaller, plus I'm making an overall limit.

Just curious, what is the Model\Invoice::query() function?

Harro

It's an ORM model.

Harro

To avoid the double query, I would probably do:
- set the total pages hard coded to something very high
- configure pagination to NOT show a 'last page' marker
- cache the page results
- run only the limit/offset query if no cache for that page is present, and cache the result
- display an error message and go back to the previous page if the page has no results

This should give you the least amount of queries which still keep it usable for your users.

h3x

Thanks Harro.

Btw. How should I cache the results? I mean, it should make a results cache for each page and search setting. Shouldnt it?

Then I should just use something like this?

query()->cached(1500, 'scores' . $page . '_' . $custom_setting, false)->execute();

Harro

That would probably work, yes.

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.

Howdy, Stranger!

Categories

In this Discussion