Sort order of children Pages in the CMS tree/gridfield

<%- if @topic_view.topic.tags.present? %>
<%= t 'js.tagging.tags' %>: <%- @topic_view.topic.tags.each do |t| %> <%= t %> <%- end %>
<% end %>

Silverstripe Version: 4.5

I have a ProgrammeListPage class that only allows children Pages of ProgrammePage.

I need to sort the ProgrammePage children in the CMS tree and grid view by Title asc.

I assumed this

private static $default_sort = 'Title ASC';

In the ProgrammePage class would do it. It doesn’t. It explodes with an error about Title not being in the select list. So I change it to:

private static $default_sort = 'ProgrammePage.Title ASC';

No error, also no sorting.

Tried instead:

private static $default_sort = 'SiteTree.Title ASC';

No error, also no sorting.

Page extends SiteTree extends DataObject. It does work on DataObject.

I’ve read this. Is all that really necessary just to sort by.

I wouldn’t be surprised if the tree view ignores default_sort but not sure. I would expect default_sort to work in a GridField though.

It’s unfortunate that $default_sort doesn’t support the array format that you can use in ->sort() as that’s a lot easier to parse I think.

Might be worth trying adding some quotes as SS/MySQL can be picky about that I think.

private static $default_sort = '"SiteTree"."Title" ASC';

I had tried the quotes as well. I saw when SS used default sort it had them. Doesn’t appear to be required. I have DataObjects that don’t use them and work.

I tried the extension method I linked to as well, that doesn’t work either.

What MySQL version are you running? That sounds like a possible problem with the query.

5.7.

The error at least makes sense. The select does some cases across SiteTree and ProgrammePage to find which title to use. The select field gets prefixed with the table it comes from. That’s why ProgrammePage.Title or SiteTree.Title as the default_sort value stops the error. Probably should cast it to AS Title there’s probably a reason they don’t.

Still doesn’t sort it ASC any way. I created a page with Z as the title first. Then another with A. Z is always first.

I’m not so worried about prefixing the default_sort but the full error when just using Title is:

CASE WHEN “SiteTree”.“ClassName” IN (‘SilverStripe\CMS\Model\SiteTree’, ‘Page’, ‘ContactPage’, ‘ElementPage’, ‘EventListPage’, ‘EventPage’, ‘HomePage’, ‘NewsListPage’, ‘NewsPage’, ‘PinPointListPage’, ‘PinPointPage’, ‘PracticeManagementListPage’, ‘PracticeManagementPage’, ‘ProgrammeListPage’, ‘ProgrammePage’, ‘ResourceListPage’, ‘ResourcePage’, ‘SilverStripe\ErrorPage\ErrorPage’, ‘SilverStripe\CMS\Model\RedirectorPage’, ‘SilverStripe\CMS\Model\VirtualPage’) THEN “SiteTree”.“Title” WHEN “SiteTree”.“ClassName” IN (‘ProgrammePage’) THEN “ProgrammePage”.“Title” ELSE NULL END AS “Title”, “SiteTree”.“MenuTitle”, “SiteTree”.“Content”, “SiteTree”.“MetaDescription”, “SiteTree”.“ExtraMeta”, “SiteTree”.“ShowInMenus”, “SiteTree”.“ShowInSearch”, “SiteTree”.“Sort”, “SiteTree”.“HasBrokenFile”, “SiteTree”.“HasBrokenLink”, “SiteTree”.“ReportClass”, “SiteTree”.“ParentID”, “ProgrammePage”.“MetaPageTitle”, “ProgrammePage”.“Excerpt”, “ProgrammePage”.“ShortDescription”, “ProgrammePage”.“HeroImageCaption”, “ProgrammePage”.“ReviewDate”, “ProgrammePage”.“ElementalAreaID”, “ProgrammePage”.“PageOwnerID”, “ProgrammePage”.“RegionID”, “ProgrammePage”.“AgeID”, “SiteTree”.“ID”, CASE WHEN “SiteTree”.“ClassName” IS NOT NULL THEN “SiteTree”.“ClassName” ELSE ‘SilverStripe\CMS\Model\SiteTree’ END AS “RecordClassName” FROM “SiteTree” LEFT JOIN “ProgrammePage” ON “ProgrammePage”.“ID” = “SiteTree”.“ID” WHERE (“ProgrammePage”.“ElementalAreaID” IS NULL OR “ProgrammePage”.“ElementalAreaID” = 0) AND (“SiteTree”.“ClassName” IN (?)) ORDER BY “ProgrammePage”.“Title” ASC HY000-3065: Expression #1 of ORDER BY clause is not in SELECT list, references column ‘pinnaclepractises_local.ProgrammePage.Title’ which is not in SELECT list; this is incompatible with DISTINCT

This is a long shot but I once took over a project and there were some page types which had Title defined in the $db_fields. It was redundant because Title is already defined on SiteTree. I recall it mostly worked fine and seemed harmless, but it tripped up the ORM on some edge cases because it was looking for Title on the wrong table.

You’re exactly right to fix the SQL error above @JonoM

It still doesn’t sort by default sort. It appears to not be used at all. If I deliberately break it with a unknown field name - it’ll error on dev/build. It won’t error when loading the child pages in the CMS.

Is there no known way to set the default sort child pages?