SilverStripe sort by has_one relation field "title"

Question

I have two objects Schedule and LocationPage. Object Schedule has a $has_one relation to LocationPage:

class Schedule extends DataObject {

    private static $db = array(
        'Date' => 'Date',
    );

    private static $has_one = array(
        'Location' => 'LocationPage',
    );
}

and

class LocationPage extends Page {

    private static $db = [
        'Heading' => 'HTMLVarchar(250)',
        'SubHeading' => 'Varchar(250)'
    ];
}

When I try to sort by the relation field Title it gives me an error. Here is the sort code:

 Schedule::get()->sort(['Location.Title' => 'ASC']);

Here is the sort error that I get when calling the above code:

[User Error] Uncaught SS_DatabaseException: Couldn't run query: SELECT DISTINCT "Schedule"."ClassName", "Schedule"."LastEdited", "Schedule"."Created", "Schedule"."Date", "Schedule"."LocationID", "Schedule"."ID", CASE WHEN "Schedule"."ClassName" IS NOT NULL THEN "Schedule"."ClassName" ELSE 'Schedule' END AS "RecordClassName", "LocationPage"."Title" AS "_SortColumn0" FROM "Schedule" LEFT JOIN "LocationPage" ON "LocationPage"."ID" = "Schedule"."LocationID" INNER JOIN "Page" ON "LocationPage"."ID" = "Page"."ID" INNER JOIN "SiteTree" ON "LocationPage"."ID" = "SiteTree"."ID" ORDER BY "_SortColumn0" ASC Unknown column 'LocationPage.Title' in 'field list'

What is causing this problem?

Bonkers

It seams that this is Silverstripe bug. I opened the issue. Hopefully they fix this. – Bonkers

The issue, for reference: github.com/silverstripe/silverstripe-framework/issues/7029 – Luganda

Answer 1

A workaround for this issue is to make the has_one relationship point to SiteTree instead of LocationPage.

Bestow