Love Fuel?    Donate

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.
pagination issue with missing posts
  • I observed that pagination remove some posts, exemple with query and query+pagination :

    $posts = \Model_Post::query();
    \Debug::dump($posts->count()); // output 18
    $posts = $posts->offset($pagination->offset)
                   ->limit($pagination->per_page)
                   ->order_by($by, $desc);
    \Debug::dump($posts->count()); // output 16

    What I missed ?

    thanks
  • Harro VertonHarro Verton
    Accepted Answer
    Depends on what offset and per_page is?

    count() returns the count in the resultset, not in the table.

    Enable the profiler and check the SQL that is generated.
  • hmm let me look that, refresh me before how enable it ? thanks !
  • When I do :
    $posts = \Model_Post::query()->related('categories')->where('categories.id', 5)->get()
    \Debug::dump($posts);
    // OUTPUT : 3 posts

    $posts = \Model_Post::query()->related('categories')->where('categories.id', 5)
                    ->order_by($by, $desc)
                    ->offset($pagination->offset)
                    ->limit($pagination->per_page)
                     ->get();
    \Debug::dump($posts);
    // OUTPUT 2 posts

    1 post is missing in the query, I looked in DB there is really 3 posts with category ID10
  • Harro VertonHarro Verton
    Accepted Answer
    So, again, what is the offset and the per_page? Dump $pagination to verify. 

    You enable the profiler in your app config.php, and then you enable database profiling per connection in your db.php.
  • here the profile and log :
    https://bin.fuelphp.com/snippet/view/OG

    So here offset : 0 and per_page = 5

    Thanks
  • Harro VertonHarro Verton
    Accepted Answer
    The queries you post don't match the code you posted.

    But I see the problem, your information wasn't complete, it is not a simple query, it is a query with relations.

    What you have is documented here: https://fuelphp.com/docs/packages/orm/intro.html#/troubleshooting under "I have defined a limit and offset, but the results are not correct".

    Your count() returns the number of parent records, while your query uses limit() with limits the result of the entire query, which due to the JOIN contains duplicate parent records. Which means in case of a many relation, you may end up with less parent records after hydration.

    From an ORM point of view, it is not logical to want to use pagination in combination with relations, as you will get a result like:
    A - 1
    A - 2
    B - 1 
    B - 2
    B - 3
    so you're not paginating posts, your paginating post categories. This example also shows why you can get 2 parent records when per page = 5, as the the first parent yields two records, the second three, making a total of five.

    You can use rows_offset() and rows_limit() instead of offset() and limit() to force the limits on the parent only, which I think is your intended behaviour, 5 parent records, each one will all their categories.
  • Great Harro for your expertise. I will test out that today !
  • I got working super great with rows_offset() and rows_limit(), thanks a lot Harro !
  • Harro VertonHarro Verton
    Accepted Answer
    You're welcome! ;-)

Howdy, Stranger!

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

In this Discussion