title: JPA Specification + dynamic sorting for many-to-many field without duplicates
body: <p>I have a class with a lot of fields, many-to-one and many-to-many relationship and I need to add dynamic filters by some columns (from this class and by fields from connected class) and add sorting by those fields as well.</p>
  
  <p>I have an issue with filtering and sorting by many-to-many field</p>
  
  <pre><code>@Entity
  class EntityA {
  ...
  @ManyToMany
  @JoinTable (
          name = "EntityA_EntityB",
          joinColumns = { @JoinColumn(name = "EntityB") },
          inverseJoinColumns = { @JoinColumn(name = "entityb_id") }
  )
  private List&lt;EntityB&gt; bEntities;
  ...
  }
  </code></pre>
  
  <p>And I have Specification to filter EntityA by EntityB.name (I set criteriaQuery.distinct(true) to prevent duplicates, which I have without this)</p>
  
  <pre><code>public class EntityASpecifications {
  //other specifications
  ...
  
  public static Specification&lt;EntityA&gt; entityBNameContains(String query) {
      return (root, criteriaQuery, criteriaBuilder) -&gt; {
  
          if (query == null) {
              return criteriaBuilder.conjunction();
          }
          criteriaQuery.distinct(true);
  
          return getContainsPredicate(criteriaBuilder, root.join("bEntities").get("name"), query);
      };
  }
  
  private static Predicate getContainsPredicate(CriteriaBuilder criteriaBuilder, Expression&lt;String&gt; field, String query) {
  
      return (query == null) ? criteriaBuilder.conjunction() : criteriaBuilder.like(criteriaBuilder.lower(field), getContainsPattern(query));
  }
  
  private static String getContainsPattern(String searchTerm) {
  
      return (searchTerm.isEmpty()) ? "%" : "%" + searchTerm.toLowerCase() + "%";
  }
  }
  </code></pre>
  
  <p>It works fine, the issue is that when I'm trying to use sorting and this filter at the same time</p>
  
  <pre><code>entityARepository.findAll(EntityASpecifications.entityBNameContains("name"), PageRequest.of(page, size, Sort.Direction.ASC, sortColumnName));
  </code></pre>
  
  <p>It fails for fields connected to EntityA as EntityB.name (also I have some other fields with @ManyToOne that fail) with the next Exception:</p>
  
  <pre><code>Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: ORDER BY items must appear in the select list if SELECT DISTINCT is specified
  </code></pre>
  
  <p>If I remove criteriaQuery.distinct(true); everything will be okay, but I will have duplicates and I don't want to have them</p>
  
  <p>How to fix it and don't have duplicate results at the same time? </p>