title: JPA Order by column in many to many relationship
body: <p>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:</p>
  
  <pre><code>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&lt;Country&gt; 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;
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>public class CategorySpecification implements Specification&lt;Category&gt; {
  
      Country matchCountry;
  
      @Override
      public Predicate toPredicate(Root&lt;Category&gt; root, CriteriaQuery&lt;?&gt; criteriaQuery, CriteriaBuilder builder) {
  
          return builder.isMember(matchCountry, root.get("countries"));
  
      }
  }
  </code></pre>
  
  <p>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:
  <a href="https://i.stack.imgur.com/a2ujc.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/a2ujc.png" alt="enter image description here"></a>
  So when I retrieve collection of Categories for the given Country, I want it to be ordered according to this <code>sort_order</code> column.</p>
  
  <p>The corresponding SQL query looks so,thing like this:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>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)?</p>
  
  <p>EDITED:
  Tables in database corresponding to the entities:</p>
  
  <p>The one for Country
  <a href="https://i.stack.imgur.com/w5jXZ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/w5jXZ.png" alt="Country"></a></p>
  
  <p>And the One for Category:
  <a href="https://i.stack.imgur.com/yM1zp.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/yM1zp.png" alt="enter image description here"></a></p>
  
  <p>EDITED 2:
  I've managed to achieve the desired behaviour with a native query as follows:</p>
  
  <pre><code>@Repository
  public interface CategoryRepository extends JpaRepository&lt;Category, Long&gt;, JpaSpecificationExecutor&lt;Category&gt; {
  
      @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&lt;Category&gt; findCategoriesForCountryOrdered(@Param("countryIso") String countryIso);
  }
  </code></pre>
  
  <p>But still hope to have a more JPA/Hibernate way of doing this thing</p>