title: Order by multi-column that include sort by field collection size spring jpa repository
body: <p>I don't think it is duplicate of <a href="https://stackoverflow.com/questions/23136469/order-by-count-using-spring-data-jparepository">Order by count using Spring Data JpaRepository</a>.</p>
  
  <p>Let me explain my problem:-</p>
  
  <p>I have a domain object called Account that consists of name, status, list of group objects, list of membership objects.</p>
  
  <p>for example:-</p>
  
  <pre><code>@Entity
  public class Account {
      private String name;
      private String status;
  
      @OneToMany( cascade = CascadeType.ALL, mappedBy = "account",orphanRemoval = true)
      private List&lt;Group&gt; groups;
  
      @OneToMany(mappedBy = "account", cascade = CascadeType.ALL, orphanRemoval = true)
      private List&lt;Membership&gt; memberships;
  
  
      // getters and setters..
  
  }
  
  
  @Eintity
  public class Group{
      // fields and getters setters..
  }
  
  
  @Eintity
  public class Membership{
      // fields and getters setters..
  }
  </code></pre>
  
  <p>I am using spring data JPA repository to find all accounts and passing pageable and querydsl predicate arguments(for instance <code>Page&lt;Account&gt; allAccountsPage = accountRepository.findAll(querydslPredicate, pageable);</code>). If I just want to sort accounts by name and/or status it worked great using pageable but if I want to sort by groups size and/or memberships size JPA repository is not working for me.</p>
  
  <p>I found one possible solution(i.e. <a href="https://stackoverflow.com/questions/23136469/order-by-count-using-spring-data-jparepository">Order by count using Spring Data JpaRepository</a>) but that is good for just single column sorting but I need to implement multi-column sorting that may include name, status, groups(by size), memberships(by size) and also in any order.</p>
  
  <p><strong>Is there any possible and graceful way to do that?</strong></p>
  
  <p><strong>EDIT:-</strong></p>
  
  <p>Found a way using hibernate's @Formula annotation.</p>
  
  <p>Here is a way:-</p>
  
  <pre><code>@Entity
      public class Account {
          private String name;
          private String status;
  
          @OneToMany( cascade = CascadeType.ALL, mappedBy = "account",orphanRemoval = true)
          private List&lt;Group&gt; groups;
  
          @OneToMany(mappedBy = "account", cascade = CascadeType.ALL, orphanRemoval = true)
          private List&lt;Membership&gt; memberships;
  
  
      @Formula("(select count(*) from groups where groups.account_id=id)")
      private long groupSize;
  
  
      @Formula("(select count(*) from membership where membership.account_id=id)")
      private long membershipSize;
  
          // getters and setters..
  
      }
  </code></pre>
  
  <p>Now simply use hibernate criteria to find all accounts and instead of sort by group and/or membership now you can sort it by groupSize and membershipSize.</p>
  
  <p>But that's not a good way to do that because @formula creates a subquery that makes your query slow. Also, for every * select query on Account hibernate hit subquery to get groupSize and membershipSize that makes every query very slow.</p>
  
  <p>So above solution is not a graceful way to solve my problem. <strong>Can anybody suggest better solution than that?</strong> </p>