Sorting pages by Page field

[thetuningspoon]

I am trying to sort a PageArray by a Page field. Since I want the sort to be based on the Page field's Title field, I am using a subselector:

 

$this->pages->get('template=People')->children("sort=location_select.title, limit=30");

 

The error I get is:

Error: Exception: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'example._sort_page_location_select_title.data' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (in /examplepath/wire/core/PageFinder.php line 384)

 

Removing the subselector results in the same error as well, though sorting by any field that is not a Page field seems to work fine.

[arjen]

This should be possible on Page fields. I've used it plenty of times. First things come to mind:

1. What PW version?
2. Is it a single or multi Page field?
3. What if you sort on another field besides title? I.e. location_select.name or location_select.id?

You could always create a new PageArray based on a foreach and sort again. Not ideal, but might get it working for now.

[thetuningspoon]

Thanks arjen. I was thinking that it should definitely be possible (and I feel like I've done this type of sort before without any problems).

1. PW 3.0.28 (just upgraded it to see if that would fix the issue)

2. This particular one is a single Page field, though I have also tried it with a multi-page field (should that work? i.e. pagefield.first.title?)

3. Just tried that. Didn't work

 

I also set the fields to auto-join.

[BitPoet]

Have a look at the sql_mode setting of your MySQL server. This looks like ONLY_FULL_GROUP_BY  is enabled.

[thetuningspoon]

Thank-you BitPoet. That was it!  I logged into MySQL on the server and used the following to fix it:

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

This was a brand new server that my boss had just spun up and moved the site to. I hadn't thought of whether a configuration issue could be the cause.

 

pagefield.first.title doesn't seem to work on the multi-page fields, but just pagefield.title does, and it seems to behave as I would expect pagefield.first.title to behave.