title: How do I port query with join across multiple tables from JDOQL to HQL
body: <p>I am porting an application for KodoJDO to Hibernate.
  I have a query that goes across 4 tables in the db, and 3 objects in the java code.</p>
  
  <p>In English the query is Find the users that have entitlements in system X.</p>
  
  <p>my JDOQL where clause called on the User object was
      where entitlements.contains(ent) &amp;&amp; (upper( ent.system.id ) = 'EVPN')</p>
  
  <p>some sql that does the query is:</p>
  
  <pre><code> select unique(u.id)
   from USER u, USERENTITLEMENT ue, ENTITLEMENT e, SYSTEM s
   where u.id = ue.userid
   and ue.entitlementid = e.id
   and e.systemid = s.id
   and s.id = 'evpn'
  </code></pre>
  
  <p>My best guess for HQL gives me an exception</p>
  
  <pre><code>org.hibernate.hql.ast.QuerySyntaxException: unexpected AST node: ( [select user from com.ebig.entity.User as user, com.ebig.entity.Entitlement as ent, com.ebig.entity.System as sys where  entitlements.contains(ent) and ent.system = sys and sys.id  = 'evpn']
  </code></pre>
  
  <p>the db is structured like this:</p>
  
  <pre><code> User
     id
  
   UserEntitlement
     userid
     entitlementid
  
   Entitlement
     id
     systemid
  
   System
     id
  </code></pre>
  
  <p>the java code is structured as below:</p>
  
  <pre><code> class User 
   {
      String id;
      Set&lt;Entitlement&gt; entitlements;
   }
  
   class Entitlement
   {
      String id;
      System system;
   }
  
   class System
   {
      String id;
   }
  </code></pre>
  
  <p><strong>Update</strong> My final query that works</p>
  
  <pre><code>            hqlQuery = "select distinct user from User as user "+
          "inner join user.entitlements as entitlement inner join entitlement.system as system "+
          "where  system.id  = 'evpn'  AND mod(user.flags, 2) = 0  AND source = 1";
  </code></pre>
  
  <p>Yes I know I should use parameters, but I have a great many problems to solve, and will post pone that code for another day.</p>
  
  <p>Another variation with an implicit inner join for entitlement to system</p>
  
  <pre><code>        hqlQuery = "select distinct user from User as user "+
          "inner join user.entitlements as entitlement "+
          "where  entitlement.system.id  = 'evpn'  AND mod(user.flags, 2) = 0  AND source = 1";
  </code></pre>