Welcome Guest, Not a member yet? Register   Sign In
Just when I thought I had Pagination put to bed...
#1
Question 
(This post was last modified: 11-03-2020, 01:56 PM by Gary.)

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.
Reply
#2

(This post was last modified: 11-03-2020, 05:15 PM by ojmichael.)

Try changing the storage engine to InnoDB.
Reply
#3

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)

Learning CI4 from my works, from errors and how to fix bugs in the community

Love CI & Thanks CI Teams

Reply
#4

(This post was last modified: 11-04-2020, 12:01 PM by Gary.)

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...
Reply
#5

With order by you should always specify both asc desc for the fields in the order that you want
them returned to.
What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )
Reply
#6

The problem lies in the data structure and the ability to use SQL

Learning CI4 from my works, from errors and how to fix bugs in the community

Love CI & Thanks CI Teams

Reply
#7

Do you have an index on the date field?
Reply
#8

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 (?).
Reply
#9

CodeIgniter 4 uses findAll() for the pagination you can see this in the Model file.
What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )
Reply




Theme © iAndrew 2016 - Forum software by © MyBB