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?

score 17 · Accepted Answer · edited Dec 02 '17 at 20:10

17

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');

edited Dec 02 '17 at 20:10

ekhumoro

115,249
20
229
336

answered Dec 02 '17 at 19:35

Ahmed Aboelsaoud

351
2
6

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! – Stoff Dec 06 '17 at 15:03
1

This command is not related to laravel but is related to the database engine https://stackoverflow.com/questions/6662837/how-mysql-order-the-rows-with-same-values?answertab=votes#tab-top – Ahmed Aboelsaoud Dec 09 '17 at 20:19
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! – Stoff Jan 16 '18 at 08:41

score 2 · Answer 2 · answered May 05 '17 at 06:50

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');

answered May 05 '17 at 06:50

Cyril

3,048
4
26
29

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.) – Stoff May 05 '17 at 06:54
AH yes I needed distinct – FosAvance May 22 '23 at 08:34

score 0 · Answer 3 · answered May 05 '17 at 07:09

0

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()

answered May 05 '17 at 07:09

Cyril

3,048
4
26
29

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. – Stoff May 05 '17 at 07:33

Laravel pagination showing duplicate and replacing random row

3 Answers3