Laravel pagination showing duplicate and replacing random row

Question

I am using laravel 5.4 with pagination and I have an issue where a row from my database shows up twice, once on two out of the 4 pages. When I delete the row however both are removed but the total count of rows using the count only shows -1 and then I see a previously hidden/replaced row.

See data below:

Straight from Database, query is set up to show all of these rows + Query:

SELECT *  
FROM `faulty_device` 
WHERE `fault_id` = 14 
AND `status` < 3

(Bold is precented twice in laravel and cursive is not showing at all)

|4254|11383|14|NULL|XXXXXXXXXX|1|NULL|2017-05-05 07:07:43 |4208|10411|14|NULL|XXXXXXXXXX|1|NULL|2017-05-04 07:16:03 |4207|10313|14|NULL|XXXXXXXXXX|1|NULL|2017-05-04 07:16:03 |4206|10229|14|NULL|XXXXXXXXXX|1|NULL|2017-05-04 07:16:03 (Not showing) |4205|9527|14|NULL|XXXXXXXXXX|1|NULL|2017-05-04 07:16:03 |4204|8538|14|NULL|XXXXXXXXXX|1|NULL|2017-05-04 07:16:03 |4203|8457|14|NULL|XXXXXXXXXX|1|NULL|2017-05-04 07:16:03 |4202|8454|14|NULL|XXXXXXXXXX|1|NULL|2017-05-04 07:16:03 |4201|8402|14|NULL|XXXXXXXXXX|1|NULL|2017-05-04 07:16:03 |4200|6497|14|NULL|XXXXXXXXXX|1|NULL|2017-05-04 07:16:03 |4199|6454|14|NULL|XXXXXXXXXX|1|NULL|2017-05-04 07:16:03 |4198|6384|14|NULL|XXXXXXXXXX|1|NULL|2017-05-04 07:16:03 |4209|24666|14|NULL|XXXXXXXXXX|1|NULL|2017-05-04 07:16:03 |4241|451|14|NULL|XXXXXXXXXX|1|NULL|2017-05-05 07:07:43 |4242|1526|14|NULL|XXXXXXXXXX|1|NULL|2017-05-05 07:07:43 |4253|9879|14|NULL|XXXXXXXXXX|1|NULL|2017-05-05 07:07:43 |4252|9395|14|NULL|XXXXXXXXXX|1|NULL|2017-05-05 07:07:43 |4251|9277|14|NULL|XXXXXXXXXX|1|NULL|2017-05-05 07:07:43 |4250|6074|14|NULL|XXXXXXXXXX|1|NULL|2017-05-05 07:07:43 |4249|6000|14|NULL|XXXXXXXXXX|1|NULL|2017-05-05 07:07:43 |4248|5770|14|NULL|XXXXXXXXXX|1|NULL|2017-05-05 07:07:43 |4247|4962|14|NULL|XXXXXXXXXX|1|NULL|2017-05-05 07:07:43 |4246|4740|14|NULL|XXXXXXXXXX|1|NULL|2017-05-05 07:07:43 |4245|4734|14|NULL|XXXXXXXXXX|1|NULL|2017-05-05 07:07:43 |4244|4704|14|NULL|XXXXXXXXXX|1|NULL|2017-05-05 07:07:43 |4243|2824|14|NULL|XXXXXXXXXX|1|NULL|2017-05-05 07:07:43 |4197|3910|14|NULL|XXXXXXXXXX|1|NULL|2017-05-04 07:16:03 |4196|3470|14|NULL|XXXXXXXXXX|1|NULL|2017-05-04 07:16:03 (presented twice) |4195|3357|14|NULL|XXXXXXXXXX|1|NULL|2017-05-04 07:16:03 |4155|2380|14|NULL|XXXXXXXXXX|1|NULL|2017-05-03 06:55:48 |4121|7766|14|NULL|XXXXXXXXXX|1|NULL|2017-05-02 09:43:29 |4120|7561|14|NULL|XXXXXXXXXX|1|NULL|2017-05-02 09:43:29 |4119|7318|14|NULL|XXXXXXXXXX|1|NULL|2017-05-02 09:43:29 |4118|7276|14|8|XXXXXXXXXX|2|NULL|2017-05-02 09:43:29 |4117|6782|14|NULL|XXXXXXXXXX|1|NULL|2017-05-02 09:43:29 |4116|6571|14|NULL|XXXXXXXXXX|1|NULL|2017-05-02 09:43:29 |4115|5713|14|NULL|XXXXXXXXXX|1|NULL|2017-05-02 09:43:29 |4112|4603|14|NULL|XXXXXXXXXX|1|NULL|2017-05-02 09:43:29 |4110|3633|14|NULL|XXXXXXXXXX|1|NULL|2017-05-02 09:43:29 |4106|2805|14|NULL|XXXXXXXXXX|1|NULL|2017-05-02 09:43:29 |4158|4515|14|NULL|XXXXXXXXXX|1|NULL|2017-05-03 06:55:48 |4159|5627|14|NULL|XXXXXXXXXX|1|NULL|2017-05-03 06:55:48 |4160|5628|14|NULL|XXXXXXXXXX|1|NULL|2017-05-03 06:55:48 |4194|2858|14|NULL|XXXXXXXXXX|1|NULL|2017-05-04 07:16:03 |4193|1536|14|NULL|XXXXXXXXXX|1|NULL|2017-05-04 07:16:03 |4192|849|14|NULL|XXXXXXXXXX|1|NULL|2017-05-04 07:16:03 |4168|24642|14|NULL|XXXXXXXXXX|1|NULL|2017-05-03 06:55:48 |4167|10559|14|NULL|XXXXXXXXXX|1|NULL|2017-05-03 06:55:48 |4166|10439|14|NULL|XXXXXXXXXX|1|NULL|2017-05-03 06:55:48 |4165|10142|14|NULL|XXXXXXXXXX|1|NULL|2017-05-03 06:55:48 |4164|10114|14|NULL|XXXXXXXXXX|1|NULL|2017-05-03 06:55:48 |4163|8777|14|NULL|XXXXXXXXXX|1|NULL|2017-05-03 06:55:48 |4162|8513|14|NULL|XXXXXXXXXX|1|NULL|2017-05-03 06:55:48 |4161|7450|14|NULL|XXXXXXXXXX|1|NULL|2017-05-03 06:55:48 |2933|6841|14|NULL|XXXXXXXXXX|1|NULL|2017-04-08 10:55:36

Basically the presented twice row removes the Not Showing row. When I manually change status on that row to 3 or up(Not included in query therefor will not show) the row that is shown twice, the previously hidden row shows once more. It is like this duplicate is a charmelion just taking someones slot randomly..

Laravel pagination presented data + query : (I am aware this could be made to look prettier, but It is supposed to do the same thing Which it actually is since if i use a count here it will show 55 rows, just like if i did a count on above)

$faultyDevices = FaultyDevice::
where('status', '!=', '3')
->where('status', '!=', '4')
->where('status', '!=', '5')
->where('status', '!=', '6')
->where('status', '!=', '7')
->orderBy('created_at', 'asc')
->paginate(18, ['*'], 'faults_page');

Here below comes a pagination with a max of 60 so that all the rows show on one page instead of being split to 4. Here it shows all rows correctly, just like when doing the raw SQL in the database:

Any ideas how to fix? Surely this has to be a bug, and not my bad code?

Ziagos

Answer 1

This is because there are many records with the same created_at value. To fix this, add a second order by id to remove the duplicated show in pagination:

$faultyDevices = FaultyDevice::
    where('status', '!=', '3')
    ->where('status', '!=', '4')
    ->where('status', '!=', '5')
    ->where('status', '!=', '6')
    ->where('status', '!=', '7')
    ->orderBy('created_at', 'asc')
    ->orderBy('id', 'asc')
    ->paginate(18, ['*'], 'faults_page');

Boonie

This sounds probable. I would argue that laravel should not order differently depending on page regardless of the same created_at value, but I do understand that this is probably the issue. Since none knew about this, I have since swapped to show the data in another datatable which works with jquery and therefor the issue does not present itself any longer - So i cannot confirm. But again, sounds very probable! – Ziagos

This command is not related to laravel but is related to the database engine #6663337 – Boonie

Gotcha. Would't have imagined that there was no built in method for always showing items in the same order, even if they are identical on the order. Thanks! – Ziagos

Answer 2

To avoid showing duplicates, add a distinct() clause to your query:

$faultyDevices = FaultyDevice
    ::distinct()
    ->where('status', '<>', 3)
    ->where('status', '<>', 4)
    ->where('status', '<>', 5)
    ->where('status', '<>', 6)
    ->where('status', '<>', 7)
    ->orderBy('created_at', 'asc')
    ->paginate(18, ['*'], 'faults_page');

Evvie

This is not the issue I am afraid. Please look through the issue. There is NO duplicates in the database, therefor it showing the same item on different pages is quite.. odd. And it does not present the item twice if i make all rows present on one larger page (paginate 60 etc instead of 18.) – Ziagos

AH yes I needed distinct – Jelena

Answer 3

Are you sure that the table isn't being updated as you click to change page? Basically, Laravel is just counting the rows of the full search, and sets offset and limit according to that number and the page you want to display. There cannot be a bug in that part of the job.

But if some new rows have been inserted after the pagination was printed, then when you navigate to the next page, you will see rows from the previous page. It is normal and there's no way to avoid this unless creating a new pagination system based on the id rather than the row count()

Evvie

I am sure. Laravel is doing its job great until the splitting of pages. As you can see, laravel pagination is ordered by the created at, therefor new inserts will never appear in the middle - regardless though, I have moved this to my testing environment with no new inserts and even if it did insert, the inserts themself is unique. The same "Device" as we call them can never have two listings with the same status and therefor duplicates will never happen. Check the two pictures provided and you will see that they differ, even though it is the same data and even the same laravel query. – Ziagos