Autojoin in complex setups (page field with multiple pages)

[ceberlin]

A conversation from the GITHUB issues which I thought to be useful to have in this Forum's knowledge base.

Ryan Cramer put some valuable information there, which should be searchable in this forum, i.m.h.o.

My Summary:

Use the "autojoin" option on pagefield (when allowing multiple pages) with care. 

There could be unwanted side effects especially if you use more than one of these fields with that setup on a single template.

Original conversation:

tbba asked:

I have 2 page fields (one is the clone of the other - so they are unique apart from the name).

Both are used on a template.
The first field DOES remember the (dragged) sort order of the pages, the second does NOT.

Has anyone else have the problem too?
Is this a cache problem, or corrupt indexes?

PW latest dev / php 5.6 fastcgi / Safari+Firefox

Update: If I change the display order of those 2 fields in the template, both fields behave the opposite:
So the first (whatever the first field is in the template when editing the page) always WORKS the second NOT. (The 2nd field lets me still adding or deleting a page - just the order is corrupt there.)

 

Update: On localhost everything works (same php version but has opcache off in MAMP - maybe this info is important)

Update: If I switch "autojoin" off for those page fields, everything works everywhere. 
This is my workaround for now.

(I remember that "autojoin" has been a problem for me with page fields already in much earlier versions of PW on many sites so it is not a temporary phenomena.)

ryancramerdesign commented

If you need to retain a sort order, you'd only want to autojoin one of the page fields. Autojoin causes everything to be loaded in one query, and there can't be two different sorted result sets included in that query as far as I know. Though it's interesting it works on your localhost and not on the other server. There are some MySQL versions known to have issues with sorting and certain queries, and you may be hitting up against that on one server and not the other. Though I can definitely see how auto joining two Page fields in one query would pose challenges to retaining the sort order. So in either case, I would limit your autojoin to just one Page field, or simply not use Autojoin for page fields at all. There's not a major benefit in doing so.  

tbba commented

MYSQL on the live server is 5.6 and on localhost/MAMP it is still 5.5.

I am very glad you can confirm that this issue is not a phantom of a defect system and can be 100% nailed down to "Autojoin". 

I used Autojoin under the assumption that it indexes a field and makes certain searches (menu building) quicker. (Since the new cache options, I am not worried with speed on certain searches any more.)

If Autojoin has, in the best case, no "major" effect with page fields and in the worst case totally confuses the system, why is the option nor switched off and deactivated for this field type? 

Is Autojoin only bad for page arrays or even if the page field is set to accept only one page? 
Are there other fields that also have a problem with "Autojoin"?

(I must say this "mystery bug" - which sometimes happens and sometimes not - was quite a debugging nightmare and other users should be warned/hindered to set-up such a combination, i.m.h.o.)

ryancramerdesign commented

I am very glad you can confirm that this issue is not a phantom of a defect system and can be 100% nailed down to "Autojoin".

 

Actually I can't nail it down to autojoin, but outside of using it with a simple text or number field, autojoin is an advanced option that can be affected by a lot of different factors. It's one of those things we suggest using when you find it helps, and don't use when you find it interferes. When you autojoin a multi-value field, MySQL is performing what's called a group_concat, and is at the mercy of certain MySQL memory buffers and configuration settings. It's very possible you are hitting up against that too, which is made all the more likely by auto joining two multi-value fields. That would explain why you see it on one server and not another.

I used Autojoin under the assumption that it indexes a field and makes certain searches (menu building) quicker. (Since the new cache options, I am not worried with speed on certain searches any more.)

It can make things quicker but it can also slow them down. It really depends on the case. The only way to tell for certain in a given instance is by testing and timing it. I do know for certain that auto-joining one or two text fields (like title and summary, for example) does provide a performance benefit to large navigation lists that use the autojoined fields, though not likely one that you would feel unless loading hundreds of thousands of pages. 

If Autojoin has, in the best case, no "major" effect with page fields and in the worst case totally confuses the system, why is the option nor switched off and deactivated for this field type? 

Because it is an advanced option that may be beneficial. If auto joining one multi-value field, like a small list of category page references, you may find it beneficial when outputting a 100 item navigation list. Or you may not. It really does come down to installation specific configuration, so it's one of those things you want to test when you use it to make sure it's working and worthwhile for your case. I wouldn't want to disable the option for this Fieldtype, because I think it can be worthwhile. But it's on the advanced tab for a reason. 

Is Autojoin only bad for page arrays or even if the page field is set to accept only one page? 
Are there other fields that also have a problem with "Autojoin"?

Autojoin should be fine for Page fields, especially single page fields. What I would avoid is giving several multi-value Page fields autojoin on the same template. You are more likely to run into issues there, but again, the only way to know for certain if it's going to be an issue on your installation is to test it. If you need something guaranteed to be portable across systems, then limit autojoin use to single-value text fields and such. Any multi-value Fieldtype has potential to run into installation-specific limits that could cause issues, so always treat it as an advanced option and test.

[Jeroen Diderik]

I had an issue with a PageField too, although not with 2 PageFields on the same page, I ran into a limit.

In short: if you have more then 170 (in my case reproducable) referenced pages in the field, AND the 'AutoJoin' is set, then when selecting more then 170 items (using for example SelectMultiple), after saving the page, the API will only return the first 170 items...and thus only 170 will be selected if you re-open the page in the backend.

Here's my post on it in the forum

[Solution Innovators]

We are also seeing problems with autojoining fields that have multiple values, including page fields and table fields. For page fields, only the first item is returned. For table fields, nothing is returned. 

[Jonathan Lahijani]

On 8/18/2015 at 12:18 PM, Jeroen Diderik said:

I had an issue with a PageField too, although not with 2 PageFields on the same page, I ran into a limit.

In short: if you have more then 170 (in my case reproducable) referenced pages in the field, AND the 'AutoJoin' is set, then when selecting more then 170 items (using for example SelectMultiple), after saving the page, the API will only return the first 170 items...and thus only 170 will be selected if you re-open the page in the backend.

Here's my post on it in the forum

Related: https://github.com/processwire/processwire-issues/issues/928

[bernhard]

RockFinder2 might be an option ?