Custom entityquery sorting

I have a normal "surname" and "firstname" fields, however I need something more than

->sort('field_surname', 'ASC')
->sort('field_firstname', 'ASC')

The surnames are old English names and contain prefixes to the names so a surname of "St John" would need to be sorted as "Saint John" to appear in the list in the correct place.

Other examples could contain an apostrophe which would need to be removed for the sort, but still displayed.

Ideally I'd like a nice pre-written module to do this, but suspect there's not one.

Is there any way to add a custom sort to an entity query? I will probably need to use this same sort in a view so something that is re-usable (for example in a view) would be ideal.

Entity query only has pretty basic sort available.

You could write a more complicated query such as a SELECT query.

However, given that you want to reuse this in Views, and given that you probably have several rules that you want to apply, I would just add another field ("name for sorting") and then use hook_entity_presave() to compute the value by using whatever rules you want.

Using hook_entity_presave() ensures that the value will be updated when the node is created or edited, so it will always be current.

You can hide this field in the entity display settings and then sort by it in Views, entityquery, and anything else you want.

The downside of this approach is that you have another field in the database, but I think this approach is easier to maintain and troubleshoot than a complicated sort.

In case it helps others, I resolved this by pulling all the records with a basic sort and then using PHP's usort with a bespoke function to pull back the desired fields from the node object, manipulate and compare and then display as needed.

$node_ids = \Drupal::entityQuery('node')
            ->condition('type', 'my_type', '=')
            ->execute();    
$nodes = \Drupal::entityTypeManager()->getStorage('node')->loadMultiple($node_ids);
usort($nodes, [$this, "my_node_sort"]);

It sorted around 1800 complex nodes with no noticeable delay, so may not be a perfect solution, but workable. Also came in useful for another sort where it was not a straight alphabetical sort. Have not looked into Views, but suspect I can use a similar process.