[Use Case]: Sort collation

[FroMage]

As a ...

• Application user/user of the configuration itself
• API user (application developer)
• SPI user (container or runtime developer)
• Specification implementer

I need to be able to ...

Specify how comparisons and ordering are collated, such as by specifying a locale

Which enables me to ...

Handle more fine-grained ordering of strings.

Additional information

Using the Sort API, I can specify case insensitivity: https://jakarta.ee/specifications/data/1.1/apidocs/jakarta.data/jakarta/data/sort

But we've had users who expect to be able ot specify how the case sensitivity is collated, such as specifying the locale to use for ordering.

See quarkusio/quarkus#31254

This is coming from an original Mongo/Panache implementation which supports this, but we know Hibernate ORM also supports collation.

@yrodiere suggested that besides ordering, this might be useful as a query parameter too:

@Find
List<Book> books(@Collate("ucs_basic") String title);

Perhaps we should handle this use-case as well?

[gavinking]

This is the sort of thing I'm inclined to leave to implementations, simply because this annotation is never going to be portable between databases. Only two relational platforms support it, and IIRC they don't support a single collation name in common.

[FroMage]

I'd be fine with that. And we can leave @Collate to implementations easily.

However, I am unsure how to leave extra Sort properties to implementations?

[gavinking]

You mean to specify the collation dynamically?

[FroMage]

Yes. Or even statically, but… something's missing:

@Find
List<Book> books(Order order);
…
repository.books(Order.by(Sort.descIgnoreCase("title").specifyCollation(Locale.FR)));

But this is more general: what's the API pattern we can use so that implementations can define additional implementation-specific properties for Sort?

[gavinking]

I mean, implementations can define their own Sort-like types if they need to extend ... just like Hibernate's Order class.

Sure, we could in principle introduce JPA-style Options for these sorts of extensions but honestly I feel like that would be going a bit far at this stage.

[njr-11]

Additionally, a vendor could provide its own extensions of the metamodel *Attribute classes, to allow,

repository.books(Order.by(_Book.title.descIgnoreCase().specifyCollation(Locale.FR)));

[FroMage]

I mean, implementations can define their own Sort-like types if they need to extend ... just like Hibernate's Order class.

Oh no, please, I've just changed the Panache Next API to remove our types in favour of the JD ones, and I'm cursing that ORM and JPA have their own. Namespace pollution is real, and nobody can figure out what type to use.

Sure, we could in principle introduce JPA-style Options for these sorts of extensions but honestly I feel like that would be going a bit far at this stage.

I'm fine with leaving that for later.

[FroMage]

Additionally, a vendor could provide its own extensions of the metamodel *Attribute classes, to allow,

repository.books(Order.by(_Book.title.descIgnoreCase().specifyCollation(Locale.FR)));

OK, so an implementation would provide their own subtype of TextAttribute with their own subtype of Sort then?

I guess that would work. If this becomes the official strategy, perhaps it makes sense to write it down in the spec so that implementors and users know where these extension points belong?

[njr-11]

I didn't mean to say it is the official strategy, only that it is possible for a vendor to do.
It would be preferable if vendors ask about standardizing extensions they might want to add so we can consider if it would be beneficial to standardize them in the spec and benefit all implementations (and users). So thanks for opening this to consider!

[FroMage]

Of course. But this sort of extension question is likely to come up in the future, and I'm not sure that people will find the answer here in this issue. That's why I think it would be useful to have it somewhere where a vendor would find it.

[gavinking]

I mean to me the biggest issue here is that we don't allow sorting by generic expressions. That's a much bigger issue than lack of support for collations, which, honestly, I have rarely seen used, and if we did allow expressions, then we could treat collate as a function, as in HQL.

[FroMage]

That would work too.