Love Fuel?    Donate

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.
Pagination seems useless
  • h3xh3x
    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
  • HarroHarro
    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.
  • h3xh3x
    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...
  • HarroHarro
    Accepted Answer
    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...
  • h3xh3x
    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?
  • HarroHarro
    It's an ORM model.
  • HarroHarro
    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.

  • h3xh3x
    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();
  • HarroHarro
    That would probably work, yes.
Add a Comment

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Apply for Membership

Categories

In this Discussion