+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: CodeIgniter 4 (https://forum.codeigniter.com/forumdisplay.php?fid=28)
+--- Forum: CodeIgniter 4 Support (https://forum.codeigniter.com/forumdisplay.php?fid=30)
+--- Thread: Just when I thought I had Pagination put to bed... (/showthread.php?tid=77906)
Just when I thought I had Pagination put to bed... - Gary - 11-03-2020
I've experienced a hiccup with pagination... this time when it comes to the sort order of results.
If I do this:
Code:
$model->select('name, status, date, timezone, .....')->where('name', $name)->whereIn('status', STATUS_ARRAY)->orderBy('date', 'DESC');and there happens to be a many records that have the an identical date (more than what will fit on a single pagination's page), I find that the orderBy constraint causes the results presented on the different pages to frequently have repetitions of records on several pages (and some of the records that should be displayed are missed- perhaps replaced by the repeated ones?).
Without making my lazy brain do any thinking, I find after a little experimenting, that if I do this:
Code:
$model->select('name, status, date, timezone, .....')->where('name', $name)->whereIn('status', STATUS_ARRAY)->orderBy('date DESC, id DESC');the problem appears to go away.
* the 'date' field is a unix timestamp (int).
My concern is that I have many pages that use pagination (all with ordered sets of results) that will probably, at some point, have results that are ordered by a value that is repeated many times (more than what will fit on a single page)... which looks like it will then break the pagination's display (?).
So my questions are:
1) am I doing something stupid?
2) has anyone else noticed/experienced this problem of paging with an orderBy?
3) is there a more sensible workaround than adding an additional, superfluous (but unique) id field to the orderBy that just creates more work for the query (and more importantly, probably causes a table-scan vs an indexed search/sort in the database)?
Thanks.
RE: Just when I thought I had Pagination put to bed... - ojmichael - 11-03-2020
Try changing the storage engine to InnoDB.
RE: Just when I thought I had Pagination put to bed... - nc03061981 - 11-03-2020
With col status, you should use Where with one value (current you use whereIn, so there are many same records, because with 1 order there will be many states)
RE: Just when I thought I had Pagination put to bed... - Gary - 11-04-2020
Thanks for the suggestions... however:
1) The storage engine is already InnoDB.
2) whereIn() is necessary because STATUS_ARRAY is an array of acceptable statuses, not a single value (and using where() gives an error if it's not a single value/closure). Even if I did make it a single value (as opposed to the currently used array) and did use where(), most of the records in question will still be of the 'active' status, and so almost the same number will be returned as what is currently being returned...
RE: Just when I thought I had Pagination put to bed... - InsiteFX - 11-04-2020
With order by you should always specify both asc desc for the fields in the order that you want
them returned to.
RE: Just when I thought I had Pagination put to bed... - nc03061981 - 11-04-2020
The problem lies in the data structure and the ability to use SQL
RE: Just when I thought I had Pagination put to bed... - ojmichael - 11-04-2020
Do you have an index on the date field?
RE: Just when I thought I had Pagination put to bed... - Gary - 11-06-2020
There is an index on the date field, though for obvious reasons it is not unique. It is part of an index: name|status|date.
id, which is the primary key in the table IS unique, and will have it's own index, however, because it's not part of the index used for the particular query, it'll break MySQL's use of the index and always result in a table-scan (and isn't necessary for the query to work and return the correct results).
As far as I can tell, the problem only appears to happen when there are more results with the same name|status|date 'key' than what can fit on a single pagination's page.
Without having dug though the framework's code yet, it appears that CI's pagination doesn't keep track of the order of results in any way, and relies solely on the return order of the query... which is fair enough... though nothing is changing the order of the returned results at the moment?! Which then makes it look like CI looses its position in the results table between successive pagination calls (?).
RE: Just when I thought I had Pagination put to bed... - InsiteFX - 11-06-2020
CodeIgniter 4 uses findAll() for the pagination you can see this in the Model file.