Page field limit on 171 references

[Jeroen Diderik]

Hi All,

I've stumbled onto something strange.

I have a Page field, referencing to a collection of pages of a certain template.

There are 302 possible pages to potentially reference.

The field is setup as a Multipage SelectMultiple (same behaviour with Checkboxes)

When I select all the pages, it only stores a reference to the first 171.

This is concistent, on different servers, with more then 171 possible pages to reference.

It doesn't seem to be a limit of php, the data in the multipart/form is fully send and fields coming after this field are succesfully saved.

So... is there some limit on a PageArray / Page field?

Running PW 2.6.13

[Jeroen Diderik]

Further testen:

- created a addHookAfter('Pages::save', $this, 'pageSaveHook');

Logging the values of the reference field.. all selected fields are in there (when selecting all in de multiselect)

- Checked the database values in my field_card_group table (the Page field is called card_group), all are there and saved properly...

So... conclusion: drawing the multiselect or checkboxes... somewhere after 171 items, the others are not set selected or checked ...

Search continues

[Jeroen Diderik]

Got a little further by looking at the SQL queries run. Seems I found a bit more info:

The page containing the Page field is queried like this: (seen in the debug trace when on this page in the Backend)

SELECT false AS isLoaded,

pages.templates_id AS templates_id,

pages.*

, pages_sortfields.sortfield,

(SELECT COUNT(*) FROM pages AS children WHERE children.parent_id=pages.id) AS numChildren,

field_title.data AS `title__data`,

GROUP_CONCAT(field_card_group.data SEPARATOR ',') AS `card_group__data`,

field_fb_success.data AS `fb_success__data`,field_fb_error.data AS `fb_error__data`,field_max_checkin.data AS `max_checkin__data`

FROM `pages` LEFT JOIN pages_sortfields ON pages_sortfields.pages_id=pages.id LEFT JOIN field_title ON field_title.pages_id=pages.id

LEFT JOIN field_card_group ON field_card_group.pages_id=pages.id

LEFT JOIN field_fb_success ON field_fb_success.pages_id=pages.id

LEFT JOIN field_fb_error ON field_fb_error.pages_id=pages.id

LEFT JOIN field_max_checkin ON field_max_checkin.pages_id=pages.id

WHERE pages.templates_id=61 AND pages.id IN(1341)

GROUP BY pages.id

The part:

GROUP_CONCAT(field_card_group.data SEPARATOR ',') AS `card_group__data`

is what returns a full list of id's that are referenced... so far so good, got 210 id's

But the next query that is run is:

SELECT id, templates_id FROM pages WHERE id IN(1337,1338,1353,1399,1400,1401,.. etc

This is where not all id's from the previous query are included in the IN ( ) part. it only includes 170 id's

Hope anyone has an AHA moment... not sure why this is happening... still going through the processwire Core to find out where things are done.

[LostKobrakai]

Now that's some serious debugging work. I can help you a bit with where to look at. 

core/Database*.php

core/Fieldtype(Multi).php

modules/Fieldtype/FieldtypePage.php

For the fieldtypes the __wakeupValue() method it the interesting one.

[Jeroen Diderik]

It gets more exciting by the minute...the plot thickens....

I have now traced down to the core/Pages.php

On line 447, an IDs array if filled with $ids[(int) $key] = $id;

After 170 items of my page, suddenly the parent of those pages is encountered, with $key = 0 and $id = 'the id of the parent of my referenced pages'

then it continues with the rest of the referenced pages, but starts at $key 0....

Scratch that... is first comes in with getByID for the card_group field, then for the parent of the referenced fields and then with all card_group pages.

[Jeroen Diderik]

This issue is starting to tick me off 

I can see in the SQL traces, that the first batch of 171 ID's is collected, query NR 29

Then Query 38 requests for the remainder of the batch (38 ID's)

I've really lost it... can somebody recreate the problem and maybe help out?

Still not sure if this is a local issue of my installation or it's PW.

Here's the pages setup:

Home

 -- card_groups (template=anything_other_then_card_group, simple page as container)

    -- group_1 (template=card_group)

    -- group_2

    -- group_3

    -- etc. etc. in total of 208

-- MyPage (containing pageField with input SelectMultiple, referencing template card_group)

Try and select all 208 groups on MyPage, save... and see if all 208 are still selected by scrolling down the SelectMultiple field....

[Jeroen Diderik]

I found how to fix the problem.... disable AutoJoin field on the PageReference field.....

Not sure if this is documented somewhere...that setting AutoJoin will limit your options when you have more then 170 references....

[ryan]

Good debugging work. The reason this occurs is because MySQL has a limit on the length of a return value from GROUP_CONCAT, though I think that limit is configurable in the MySQL server settings. The GROUP_CONCAT is only used for multi-value autojoins, so not something you need to be concerned about with large text fields or the like. Though with anything large, it's best not to use autojoin at all since it forces PW to load that data in memory every time the page is loaded. It might mean you could only load a few hundred pages in memory at once rather than a few thousand. Autojoin can be a nice optimization for things like "title" or a short "summary" field, or a blog post with category page references. But you would only want to use autojoin for a field that you know will always be accessed from a given $page, every time you load it, regardless of where you load it. For everything else you should leave it off.