sort() is not working on translated fields

[Friksel]

Sorting of translated fields is not working:

$this->Tags()->sort('Title', 'ASC');
Does only work on default locale, because sort is ALWAYS sorting Tags on default locale instead of current locale.

$Loc_TitleField = Fluent::db_field_for_locale('Title', $this->CurrentLocale());
$this->Tags()->sort($Loc_TitleField, 'ASC');

Is not working, because Tags that have no textual difference in translation, are null in the database and therefore mess up the sort.

[tractorcow]

Todo: Implement translatable sorting. :D

It'll probably need another line in augmentSQL() to ensure each field is sorted.

A "simple" rewrite could be ORDER BY "Tag"."Title_en_NZ" ASC, "TAG"."Title" ASC, but I guess a more complete solution would need to be a sort on expression with a COALESCE.

[robbieaverill]

Tests to prove this is a bug: https://github.com/tractorcow/silverstripe-fluent/pull/415

Currently passing for the default locale sorting but failing for the non-default (as noted in the original issue)

[tractorcow]

Also see https://github.com/tractorcow/silverstripe-fluent/issues/427

[robbieaverill]

Hey @tractorcow, I've made a WIP pull request at #415 but think I need your help to get it over the line from here

[tractorcow]

Thanks I'll try to make some time to look at it soon. :) I also want to see this fixed.

[tractorcow]

The bug in the test is that ORM doesn't support ->sort(['"somecolumn" ASC', '"anothercolumn" ASC']);. If you read the phpdocs, you're ment to key the array by column and value is the direction.

[tractorcow]

Updated the PR.

[tractorcow]

@robbieaverill you can update the core if you want to fix the above.

[tractorcow]

Fixed with https://github.com/tractorcow/silverstripe-fluent/pull/415

[robbieaverill]

Awesome!

[tractorcow]

@robbieaverill can you follow up on the minor issues I noted on the PR, namely the ->sort(['field asc', 'anotherfield desc']) syntax not working.

We may need to verify #257 doesn't still exist in new fluent.

[robbieaverill]

It's on the todo list =)

[sunnysideup]

We are still seeing this issue making a simple lookup for a gridfield fail:

SELECT DISTINCT "SiteTree"."ClassName", "SiteTree"."LastEdited", "SiteTree"."Created", "SiteTree"."URLSegment", CASE
	WHEN "SiteTree_Localised_en_NZ"."ID" IS NOT NULL THEN "SiteTree_Localised_en_NZ"."Title"
	ELSE "SiteTree"."Title" END
 AS "Title", CASE
	WHEN "SiteTree_Localised_en_NZ"."ID" IS NOT NULL THEN "SiteTree_Localised_en_NZ"."MenuTitle"
	ELSE "SiteTree"."MenuTitle" END
 AS "MenuTitle", CASE
	WHEN "SiteTree_Localised_en_NZ"."ID" IS NOT NULL THEN "SiteTree_Localised_en_NZ"."Content"
	ELSE "SiteTree"."Content" END
 AS "Content", CASE
	WHEN "SiteTree_Localised_en_NZ"."ID" IS NOT NULL THEN "SiteTree_Localised_en_NZ"."MetaDescription"
	ELSE "SiteTree"."MetaDescription" END
 AS "MetaDescription", "SiteTree"."ExtraMeta", "SiteTree"."ShowInMenus", "SiteTree"."ShowInSearch", "SiteTree"."Sort", "SiteTree"."HasBrokenFile", "SiteTree"."HasBrokenLink", CASE
	WHEN "SiteTree_Localised_en_NZ"."ID" IS NOT NULL THEN "SiteTree_Localised_en_NZ"."ReportClass"
	ELSE "SiteTree"."ReportClass" END
 AS "ReportClass", "SiteTree"."Version", "SiteTree"."CanViewType", "SiteTree"."CanEditType", "SiteTree"."ShowPageUtilities", "SiteTree"."ContentReviewType", "SiteTree"."ReviewPeriodDays", "SiteTree"."NextReviewDate", CASE
	WHEN "SiteTree_Localised_en_NZ"."ID" IS NOT NULL THEN "SiteTree_Localised_en_NZ"."LastEditedByName"
	ELSE "SiteTree"."LastEditedByName" END
 AS "LastEditedByName", CASE
	WHEN "SiteTree_Localised_en_NZ"."ID" IS NOT NULL THEN "SiteTree_Localised_en_NZ"."OwnerNames"
	ELSE "SiteTree"."OwnerNames" END
 AS "OwnerNames", CASE
	WHEN "SiteTree_Localised_en_NZ"."ID" IS NOT NULL THEN "SiteTree_Localised_en_NZ"."ShareTokenSalt"
	ELSE "SiteTree"."ShareTokenSalt" END
 AS "ShareTokenSalt", "SiteTree"."ParentID", "SiteTree"."WorkflowDefinitionID", CASE
	WHEN "Page_Localised_en_NZ"."ID" IS NOT NULL THEN "Page_Localised_en_NZ"."Intro"
	ELSE "Page"."Intro" END
 AS "Intro", "Page"."ShowInFooterMenu", "Page"."MainLinkID", "Page"."SecondaryLink1ID", "Page"."SecondaryLink2ID", "Page"."VideoHolderID", "SiteTree"."ID", 
			CASE WHEN "SiteTree"."ClassName" IS NOT NULL THEN "SiteTree"."ClassName"
			ELSE 'SilverStripe\\CMS\\Model\\SiteTree' END AS "RecordClassName", 'en_NZ' AS "Locale", CASE 	WHEN "Page_Localised_en_NZ"."ID" IS NOT NULL THEN 'en_NZ' 
ELSE NULL END AS "SourceLocale"

FROM "SiteTree"
LEFT JOIN "Page" ON "Page"."ID" = "SiteTree"."ID"
LEFT JOIN "SiteTree_Localised" AS "SiteTree_Localised_en_NZ" ON "SiteTree"."ID" = "SiteTree_Localised_en_NZ"."RecordID" AND "SiteTree_Localised_en_NZ"."Locale" = ?
LEFT JOIN "Page_Localised" AS "Page_Localised_en_NZ" ON "Page"."ID" = "Page_Localised_en_NZ"."RecordID" AND "Page_Localised_en_NZ"."Locale" = ?

WHERE ("SiteTree"."ID" NOT IN 
SELECT DISTINCT "SiteTree"."ID"

FROM "SiteTree"
LEFT JOIN "Page" ON "Page"."ID" = "SiteTree"."ID"
INNER JOIN "HouseCountTerm_DoNotAnnotateOn" ON "HouseCountTerm_DoNotAnnotateOn"."PageID" = "SiteTree"."ID"
LEFT JOIN "SiteTree_Localised" AS "SiteTree_Localised_en_NZ" ON "SiteTree"."ID" = "SiteTree_Localised_en_NZ"."RecordID" AND "SiteTree_Localised_en_NZ"."Locale" = ?
LEFT JOIN "Page_Localised" AS "Page_Localised_en_NZ" ON "Page"."ID" = "Page_Localised_en_NZ"."RecordID" AND "Page_Localised_en_NZ"."Locale" = ?

WHERE ("HouseCountTerm_DoNotAnnotateOn"."HouseCountTermID" = ?)
 AND ("SiteTree"."ClassName" IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?))))
 AND ((COALESCE("SiteTree_Localised_en_NZ"."Title", "SiteTree"."Title") LIKE ?)
 OR (COALESCE("SiteTree_Localised_en_NZ"."Content", "SiteTree"."Content") LIKE ?))
 AND ("SiteTree"."ClassName" IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?))

ORDER BY "SiteTree"."Title" ASC

It is sorted by SiteTree.Title, but this is not in the select.

Does anyone know a workaround?

[sunnysideup]

HACK:

SilverStripe\ORM\DB::query("SET SESSION sql_mode='REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE';");

ansi minus the group thingy

[lerni]

If I got it right, this issue is solved in 4 but not in 3.x. Is a backport planed?

There are workarounds for 3.x like https://github.com/SpliffSplendor/silverstripe-mysqlfixer https://github.com/lerni/silverstripe3-mysql57-fluent

@sminnee thinks it should be fixed in 3.x as well. Would this be a separate issue or reopening this one?

[sminnee]

The support for 3.x is much more limited these days so if "upgrade to SS4" is a fix to this issue, that's probably the one I'd recommend ;-)