$pages->find() and $pages->findIDs() not consistent for a given sort value

[Toutouwai]

The description for $pages->findIDs() says:

Like find() except returns array of IDs rather than Page objects

So I expected it to return equivalent results in the same order given the same selector. But this seems not be be the case:

The pages all have the same value for "modified" so I guess there is some default secondary sort value applied that isn't consistent between find() and findIDs().

PW 3.0.244

By the way, this issues repo used to have a template for new issues that seems to be missing now.

[Toutouwai]

$pages->findRaw() is different again, which is also a problem.

[ryancramerdesign]

@Toutouwai If I understand correctly, the pages you are matching all have the same "modified" value, and you are sorting by that value, without a secondary sort. There is no secondary sort. So it seems like the order would be undefined at that point., or based on whatever MySQL can optimize to return the fastest for the query. Have you tried adding a secondary sort, for instance, sort=modified, sort=id ?

[Toutouwai]

@ryancramerdesign, I became aware of this issue when developing a module that integrates with Lister. So in this case I'm not in control of the selector - it's whatever selector has been defined in Lister.

What I want to do is get the same results as Lister in the same order, but without loading full Page objects (I want to remove the limit and get all the results as IDs).

It's surprising to me that the sort order can be different like this, seeing as it's not like the order is changing randomly - I can execute the same find() or findIDs() multiple times and get the same order each time, it's just that they don't match each other. So something is determining the order.

If I can't rely on findIDs() or findRaw() to produce the same result as find(), is there some other way I can get the results of find() but only the IDs? Like, could I get the SQL query that find() is using and then strip out the joins that aren't needed?

[ryancramerdesign]

It's surprising to me that the sort order can be different like this, seeing as it's not like the order is changing randomly - I can execute the same find() or findIDs() multiple times and get the same order each time, it's just that they don't match each other. So something is determining the order.

For all of this, I think you'd have to compare the queries that it's using to be certain about what's going on.

From what I understand, if nothing changes in the query (or the data being queried), then I don't think there's any chance the order will change every time the query is executed, as MySQL will have it cached. But without a specific sort property then I think MySQL will return whatever order it can use to optimize the query and return results the fastest. If no conditions change then the order should also stay the same.

If I can't rely on findIDs() or findRaw() to produce the same result as find(), is there some other way I can get the results of find() but only the IDs? Like, could I get the SQL query that find() is using and then strip out the joins that aren't needed?

To get the result you want, I would probably go and use the PageFinder directly. That's what all those methods use underneath:

$pageFinder = new PageFinder(); 

// try with returnVerbose to false, which makes it only return IDs
$ids = $pageFinder->find("your selector string", [ 'returnVerbose' => false ]); 

// next try with returnVerbose true, which returns arrays of arrays with id, parent, template.
$data = $pageFinder->find("your selector string", [ 'returnVerbose' => true ]); 

Do either of those calls return the order you are looking for? My guess is that one of them might. If not, then you may want to have a closer look at the $pages->find() method's use of PageFinder to try and duplicate its usage.

[adrianbj]

@Toutouwai - I am sure I must be doing something different to you (and if so, sorry for the noise), but they all seem to return the same order for me:

Tracy's Debug Mode panel > Selector Queries shows these corresponding queries so it doesn't look like there is any addition ORDER BY being added.

SELECT SQL_CALC_FOUND_ROWS pages.id,pages.parent_id,pages.templates_id
FROM `pages`
JOIN field_title AS field_title ON field_title.pages_id=pages.id AND (((field_title.data LIKE '%tes%' ) ))
WHERE (pages.templates_id=29)
AND (pages.status<8192)
GROUP BY pages.id
ORDER BY pages.modified
LIMIT 0,5

SELECT SQL_CALC_FOUND_ROWS pages.id
FROM `pages`
JOIN field_title AS field_title ON field_title.pages_id=pages.id AND (((field_title.data LIKE '%tes%' ) ))
WHERE (pages.templates_id=29)
AND (pages.status<8192)
GROUP BY pages.id
ORDER BY pages.modified
LIMIT 0,5

SELECT SQL_CALC_FOUND_ROWS pages.*
FROM `pages`
JOIN field_title AS field_title ON field_title.pages_id=pages.id AND (((field_title.data LIKE '%tes%' ) ))
WHERE (pages.templates_id=29)
AND (pages.status<8192)
GROUP BY pages.id
ORDER BY pages.modified
LIMIT 0,5

[Toutouwai]

@adrianbj, it's not easy to create a reproducible test case because I don't know the factor that causes the issue. The site used for the screenshot in the first post suddenly started producing a matching result for find() and findIDs() but still a different result for findRaw(). But then I created a new test case on a different site using the same country titles and I'm getting inconsistent results again for each method.

Another things that's weird is if I do a $pages->find()...

...and I follow your suggestion of getting the SQL query from the Debug Mode panel...

and I execute that same query separately, the results are different...

@ryancramerdesign, I tried using PageFinder as you suggested and 'returnVerbose' => true is consistent with $pages->find() but 'returnVerbose' => false isn't consistent with any of the other methods.

Here's the full set of results from the new test case:

Maybe there's nothing that can be done about this. I guess I can use to a hook to force a secondary sort onto the Lister selector to avoid the issue.

[adrianbj]

@Toutouwai - the first thing I noticed is that the results from your test running the SQL directly don't even return the same page IDs.

1064, 1156, 1195, 1235, 1243 vs
1064, 1251, 1103, 1149, 1156

Any ideas what is going on there?

[Toutouwai]

@adrianbj, that's because the number of matching pages is more than the limit of the selector. The demo uses a limit to illustrate that the problem is not necessarily just that you get the same items in a different order - when a limit is applied you can get a different set of pages.