FinnOP's avatar

Pagination works incorrectly

When I use pagination then I get

Page 1

  • id=10 vote=10
  • id=2 vote=1
  • id=3 vote=1
  • id=4 vote=1
  • id=5 vote=1

Page 2

  • id=5 vote=1
  • id=4 vote=1
  • id=3 vote=1
  • id=2 vote=1
  • id=1 vote=1

I get the same item twice on different pages. These elements are gone

  • id=6 vote=1
  • id=7 vote=1
  • id=8 vote=1
  • id=9 vote=1

If you remove the ->orderBy('votes_count', 'desc') from the query, then the pagination works correctly, but without sorting

Page 1

  • id=1 vote=1
  • id=2 vote=1
  • id=3 vote=1
  • id=4 vote=1
  • id=5 vote=1

Page 2

  • id=6 vote=1
  • id=7 vote=1
  • id=8 vote=1
  • id=9 vote=1
  • id=10 vote=10

Sql Page 2

select `servers`.*, (select count(*) from `votes` where `servers`.`id` = `votes`.`server_id`) as `votes_count` from `servers` where `banned` = ? order by `votes_count` desc limit 5 offset 5

Controller

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;

use App\Server;
use App\Tag;
use App\Version;
use App\Vote;

class ServersListHomeController extends Controller
{
    protected $server;
    protected $tag;
    protected $version;
    
    public function __construct(Server $server, Tag $tag, Version $version)
    {
        $this->server = $server;
        $this->tag = $tag;
        $this->version = $version;
    }

    public function index()
    {
        $servers = Server::where('banned', 'false')->with('version', 'tags', 'statistics')->withCount('votes')->orderBy('votes_count', 'desc')->paginate(5);
        
        return view('content.home', array(
            'servers' => $servers
        ));
    }
}
0 likes
7 replies
jlrdw's avatar

You are ordering by ('votes_count', 'desc'), are you sure it's not returning correct results, just not what you expect?

FinnOP's avatar

@jlrdw The data after the ->orderBy('votes_count', 'desc') is displayed correctly. To see this, it is sufficient to use ->get() instead of ->paginate(5).

In the Russian-speaking community, it is claimed that this pagination is causing this problem.

gcwilliams's avatar

Any chance that votes is a string instead of an integer?

FinnOP's avatar

@gcwilliams The votes represented in the string votes_count is an integer.

 #attributes: array:9 [▼
          "id" => 10
          "name" => "Test one"
          "address" => "address"
          "port" => "port"
          "version_id" => 50
          "banned" => "false"
          "created_at" => "2017-07-14 16:03:25"
          "updated_at" => "2017-07-14 16:03:25"
          "votes_count" => 15
        ]
Snapey's avatar

can you try the sql query in your database toolset? Use what you have grabbed above and replace the banned =? placeholder

This will show if it is a pure mysql issue

FinnOP's avatar

@Snapey Sql I already tried. The problem is the paginator. Items that contain a unique value votes_count do not experience this problem. The problem with the elements in which the votes_count are the same.

Snapey's avatar

but the pagination only works with the 5 rows returned by the sql using take and offset.

so you are saying that the raw sql with take(5)/offset(0) and then take(5)/offset(5) give the correct rows?

Please or to participate in this conversation.