Improve performance of big query

[LostKobrakai]

The default pagination methods should work with the resulting pagearray.

[Pete Jones]

Hi, sorry to bring this up again. After looking further down the pages, we can see some improper ordering. We are getting pages that should be found with this selector

"template=horse-note, sort=-h_notes_last_comment"

appearing before pages from this selector

"template=horse-note, $session->unread_by>0, sort=-h_notes_last_comment"

 

[LostKobrakai]

If you manipulate this method (the only one) in PagesPaginatorNoDuplicates.php you could log or otherwise store found pages/selectors for each separate part. The resulting list is just a pagearray, where new pages are appended one after another.

protected function getItems($selector, $key, $start, $limit, $storage)
{
	$selector = $selector . ", start=$start, limit=$limit, id!=$storage";
	wire('log')->save('paginator', $selector);
	$pa = wire('pages')->find($selector);
	wire('log')->save('paginator', (string) $pa);
	return $pa;
}

The class(es) itself do not order things. It could be that $start / $limit values are off, but these are tested, so they should work. Could also be that some selector of yours is not working as you expect it. 

[Pete Jones]

Hi LK, we appear to have an issue with where the results are starting from on a particular page. For example, we're getting items on page 2 that have already been shown on page 1. Could this be something to do with the 'no duplicates' functionality that is somehow affecting start and end points?

[LostKobrakai]

Ah yeah, sure. The items on page1 never make it into the $storage variable for page2, which I didn't really realize. So you'd probably need to make your selectors a bit more concrete, so they don't overlap. Otherwise we're back to loading at least all id's of all items.

[Pete Jones]

Would it be best if we try to make sure there are no duplicates in the selectors inside our $paginator function, and then we can use the regular PagesPaginator?

[LostKobrakai]

Yeah, that's probably the only way for this to work without needing to exactly know which items where in pages before the current. The NoDuplicates class is more a failed attempt

[Pete Jones]

Is the best way to do this using a id!=$alreadyselectedelements? How would that work with a selector array?

[Pete Jones]

Actually, might be able to do it by being more specific in our current selectors. Will give that a go first.

[LostKobrakai]

There is no $alreadySelectedElements, because we're not loading any items before those we really need. That's the crux in pagination. It should at best work without the runtime even knowing which items came before the current page, because otherwise there will always be scalability issues. 

[Pete Jones]

Just stripping this back to basics so that I can prevent duplicates in my selectors and have noticed that the order is being controlled by the sort that I have in the selectors, rather than the order that the selectors are put into the $paginator function. That probably makes no sense, so I'll try to illustrate.

My paginator function looks like this:

$notes = $paginator(array(
	"template=horse-note, h_notes_comments.$session->unread_by>0, sort=-h_notes_last_comment",
	"template=horse-note, $session->unread_by>0, sort=-h_notes_last_comment"
), $pageNum, $limit);

h_notes_comments is a repeater with a few text fields and some checkboxes.

So what I should get is:

• pages with 1 or more of the h_notes_comments repeaters containing a checked checkbox, sorted by the h_notes_last_comment datetime field
• other pages without a checked checkbox inside the h_notes_comments repeater (or no repeater items at all), sorted by the h_notes_last_comment datetime field

But what's happening is the h_notes_last_comment field is taking precedence, so pages found with the second selector are appearing before pages found with the first, if the h_notes_last_comment happens to be more recent. Is that the expected behaviour or are we doing something wrong in our selectors?

As an aside, am i right in thinking that when we're doing

h_notes_comments.$session->unread_by>0

that will match any page that has at least one of the h_notes_last_comments repeaters with that checkbox checked? So even if there are 8 repeater items on a page, and 7 of them don't have the checkbox checked, it will still be returned?

[LostKobrakai]

You should really test your selectors first before packing it into the paginator and trying to make sense of it. But I finally managed to setup a small test case and found a few issues, not releated to sorting, but to counting the total number of pages. It seems like there's no possible solution to wrap multiple selectors, where at least one does use or-groups, to one combined to run a $pages->count() on. 

[Pete Jones]

Yes, I have noticed that renderPager() is giving more pages than needed. Before I look at that, there is a strange reordering happening. Take this pair of selectors:

$notes = $paginator(array(
		"template=horse-note, parent.h_type=2, h_notes_comments.count>0, h_notes_comments.$session->unread_by>0, sort=-h_notes_last_comment",
		"template=horse-note, parent.h_type=2, $session->unread_by>0, sort=-h_notes_last_comment"
	), $pageNum, $limit);

One of the pages from the second selector is appearing first in the list. It's definitely coming from that selector, because if I remove that from the array, that page is no longer returned. All other pages seem to be in the correct order.

I've also noticed that on page 1, where I am echoing out count($notes), this is returning 96 even though $limit is 100. It's fine on all subsequent pages though.