title: HQL query for Many To Many Associations for self referenced object
body: <p>I hava this class:</p>
  
  <pre><code>    @Entity
      @Table(name = "USERS")
      public class User {
  
      @Id
      @Column(name = "USER_ID")
      @GeneratedValue
      private long userId;
      ...
      @ManyToMany(fetch = FetchType.LAZY)
      @JoinTable(name = "FRIENDS", joinColumns = @JoinColumn(name = "USER_ID"), inverseJoinColumns = @JoinColumn(name = "FRIEND_ID"))
          private Set&lt;User&gt; friends;
  
  
      @ManyToMany(fetch = FetchType.LAZY)
      @JoinTable(name = "FRIENDS", joinColumns = @JoinColumn(name = "FRIEND_ID"), inverseJoinColumns = @JoinColumn(name = "USER_ID"))
          private Set&lt;User&gt; friendOf;
  
     ...
      public Set&lt;User&gt; getAllFriends() {
        allFriends = new HashSet&lt;User&gt;();
        allFriends.addAll(friends);
        allFriends.addAll(friendOf);
        return allFriends;
  }
     // getters and seters
  </code></pre>
  
  <p>If I need all friend of a User I can get them by simply calling getAllFriends().
  But I want to add some restriction to the maximum number of returned friends. So I want to select all friends using HQL. I want something like this (my hql is incorrect, just to show the idea):</p>
  
  <pre><code>String hql = "Select u FROM User u inner join u.friendOf fof WHERE fof.userId = :userId inner join u.friends fs WHERE fs.userId = :userId";
      Query query = sessionFactory.getCurrentSession().createQuery(hql);
      query.setParameter("userId", userId);
      List&lt;User&gt; results = query.setMaxResults(maxResults).setFirstResult(firstResult).list();
  </code></pre>
  
  <p>Is it possible to create such a query?</p>
  
  <p><strong>UPDATE:</strong> I have SQL query whiсh fits my requirements. Maybe someone can help me to translate it to HQL?</p>
  
  <pre><code>select * from users where user_id in (
  select 
   (case  
    when user_id = :userId then friend_id 
    else user_id 
   end) as id
  from friends
  where user_id = :userId or friend_id = :userId);
  </code></pre>