Extend Panache sort specification to explicitly support sorting by the size of an association of a JPA entity

[mzuber]

Description

The Panache library provides a utility class Sort to build and represent SQL sorting specifications. A Sort instance represents a list of columns to sort on, each with a direction to use for sorting, e.g.

Sort sort = Sort.by("name").and("age", Direction.Descending);
Sort sort2 = Sort.ascending("name", "age");
Sort sort3 = Sort.descending("name", "age");

In its current implementation, it is possible use the Sort class to not only sort by properties of a JPA entity (columns), but also by size of associations of the entity.

Given for example the JPA entities

@Entity
public class Person extends PanacheEntity {
    public String name;
    public String homeTown;
    @OneToMany
    public List<Dog> pets;
}

@Entity
public class Dog extends PanacheEntity {
    public String name;
    public String race;
    @ManyToOne
    public Person owner;
}

it is possible to "misuse" the interface of the Sort class to write a query like

Person.find("FROM Person p WHERE p.homeTown = :homeTown",
    Sort.by("SIZE(p.pets)", Sort.Direction.Ascending),
    Parameters.with("homeTown", "Berlin"));

It is great that this works, as this comes in especially handy when programmatically constructing the "sort property" for the query, but it doesn't feel like we are using the interface in the intended way here.

Therefore it would be great if the Sort class would provide an interface which more explicitly models this use case. For example via a method Sort.bySize(String association, Sort.Direction direction). In this case, the example above could be written like

Person.find("FROM Person p WHERE p.homeTown = :homeTown",
    Sort.bySize("p.pets", Sort.Direction.Ascending),
    Parameters.with("homeTown", "Berlin"));

Implementation ideas

Extend the Sort class with the methods

• Sort.bySize(String association)
• Sort.bySize(String... associations)
• Sort.bySize(String association, Sort.Direction direction)
• Sort.bySize(String association, Sort.NullPrecedence nullPrecedence)
• Sort.bySize(String association, Sort.Direction direction, Sort.NullPrecedence nullPrecedence)

Extend the PanacheJpaUtil.toOrderBy() method (source) to construct an ORDER BY expression for the size of an association.

[quarkus-bot]

/cc @FroMage (panache), @loicmathieu (panache)

[mzuber]

I'm happy to provide a PR if you can imagine adding this functionality.