default sort on `many_many` relation table isn't clearly documented

[MelissaWu-SS]

Affected Version

5.0.0

Description

I have a BlogPost DataObject that has many_many relation with TaxonomyTerm DataObject through BlogPostTaxonomyTerm as below:

private static array $many_many = [
    'Terms' => [
        'through' => BlogPostTaxonomyTerm::class,
        'from' => 'Parent',
        'to' => 'TaxonomyTerm',
    ],
];

There is a Sort field on the TaxonomyTerm DataObject and a separate Sort field on the many_many through relation DataObject BlogPostTaxonomyTerm.

I want to sort the linked TaxonomyTerms on the BlogPost based on the Sort value on the BlogPostTaxonomyTerm table. However, I am not able to do that. The Sorting result is always determined by the Sort value on the TaxonomyTerm DataObject instead.

Here is an example, I have the following fixture file for unit test:

SilverStripe\Taxonomy\TaxonomyTerm:
  term1:
    Name: term1
    Sort: 1
  term2:
    Name: term2
    Sort: 2
  term3:
    Name: term3
    Sort: 3

App\Pages\BlogPost:
  page1:
    Title: 'Blog Post 1'
    PublishDate: '2023-06-20 15:55:50'

App\Taxonomy\ThroughRelations\BlogPostTaxonomyTerm:
  blogPostTerm1:
    Parent: =>App\Pages\BlogPost.page1
    TaxonomyTerm: =>SilverStripe\Taxonomy\TaxonomyTerm.term3
    Sort: 1
  blogPostTerm2:
    Parent: =>App\Pages\BlogPost.page1
    TaxonomyTerm: =>SilverStripe\Taxonomy\TaxonomyTerm.term2
    Sort: 2
  blogPostTerm3:
    Parent: =>App\Pages\BlogPost.page1
    TaxonomyTerm: =>SilverStripe\Taxonomy\TaxonomyTerm.term1
    Sort: 3

And I want to test the first term of the App\Pages\BlogPost.page1.
The expected result is the first term should be "term3", however the actual first term is "term1".

After I add the following config to the BlogPostTaxonomyTerm many_many relation, the Sorting works as expected.

 private static string $default_sort = '"BlogPostTaxonomyTerm"."Sort"';

[GuySartorelli]

Note that the problem here seems to be that private static string $default_sort = 'Sort'; on the join class does not sort by the Sort field on the join class, but rather by the Sort field on the taxonomy term. To sort by the field on the join class, you need to add the table name as part of the sort config.

It will be worth checking whether adding the table name is always required, or if it's only required when there is a conflict of field names.

Most likely this is caused by the way the query is declared, where you're more-or-less doing SELECT "Sort" FROM "TaxonomyTerm" LEFT JOIN "BlogPostTaxonomyTerm" ORDER BY "Sort" - i.e. the Sort field with no alias is from the taxonomy term class, so that's what it sorts by - the ManyManyThroughList isn't taking the join's alias into account when using it to sort by.

The problem then becomes - how can we determine what the developer wants to do - they might want to sort by the Sort field on the taxonomy term itself. So... maybe this is already behaving as expected and just needs to be much more clearly communicated through documentation and PHPDoc comments.

[GuySartorelli]

In fact I think I've just convinced myself that _assuming private static string $default_sort = 'Sort'; on the join record does sort by the sort field on taxonomy term, this is purely a documentation problem. I'll move it to the docs repo.