JPA Order by column in many to many relationship

Question

My problem is a bit more complex than described from the first sight. Let me try to explain. I have two entities with unidirectional ManyToMany relationship:

public class Category implements Serializable {

    @Id
    @Column(name = "ct_id")
    @SequenceGenerator(name = "category_ct_id_seq", sequenceName = "category_ct_id_seq", allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "category_ct_id_seq")
    private Long id;

    @Column(name = "ct_name_key")
    private String nameKey;

    @ManyToMany
    @JoinTable(name = "category_2_country",
            joinColumns = @JoinColumn(name = "c2c_category_id"),
            inverseJoinColumns = @JoinColumn(name = "c2c_country_id"))
    private Set<Country> countries;
}

public class Country {

    @Id
    @SequenceGenerator(name = "country_c_id_seq", sequenceName = "country_c_id_seq", allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "country_c_id_seq")
    @Column(name = "c_id")
    private Long id;

    @Column(name = "c_name")
    private String name;

    @Column(name = "c_iso_2_alpha")
    private String isoAlpha2Code;

And I'm retrieving the collection of Categories by an ISO code of country (actually, all categories, which has relation with the requested country). For this I've written a Specification:

public class CategorySpecification implements Specification<Category> {

    Country matchCountry;

    @Override
    public Predicate toPredicate(Root<Category> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder builder) {

        return builder.isMember(matchCountry, root.get("countries"));

    }
}

This works pretty fine. The problem is I need to have order priority for the Categories in every Country. The transitive table looks like this: So when I retrieve collection of Categories for the given Country, I want it to be ordered according to this sort_order column.

The corresponding SQL query looks so,thing like this:

SELECT *
FROM category
  JOIN category_2_country ON category.ct_id = category_2_country.c2c_category_id
WHERE c2c_country_id = ?
ORDER BY sort_order;

But how should I make it in my JPA entities? And how should I persist it back to the database, it this order needs to be changed dynamically (e.g. from the admin panel)?

EDITED: Tables in database corresponding to the entities:

The one for Country

And the One for Category:

EDITED 2: I've managed to achieve the desired behaviour with a native query as follows:

@Repository
public interface CategoryRepository extends JpaRepository<Category, Long>, JpaSpecificationExecutor<Category> {

    @Query(value = "SELECT ct.*\n" +
            "FROM category ct\n" +
            "  JOIN category_2_country c2c ON ct.ct_id = c2c.c2c_category_id\n" +
            "  JOIN country c ON c2c.c2c_country_id = c.c_id\n" +
            "WHERE c.c_iso_2_alpha = :countryIso\n" +
            "ORDER BY c2c.sort_order", nativeQuery = true)
    List<Category> findCategoriesForCountryOrdered(@Param("countryIso") String countryIso);
}

But still hope to have a more JPA/Hibernate way of doing this thing

Answer 1

As you using Specification you are using JpaRepository with JpaSpecificationExecutor. JpaSpecificationExecutor api gives us a method findAll(Specification<T> spec, Sort sort).

Using Sort class, you can specify by which property (and direction) you want to sort your result list.

Here is docs: Sort

EDIT: After some searches - unfortunately, without mapping association table into hibernate, you can't order by / sort by sort_order property. This is related to JPQL which can only works on mapped properties in entities - it can't reach tables in DB if you don't map them.

So, you can map your association table and create specification, which join that two tables and creates sorting by sort_order.