title: GORM criteria query filtering where id in list
body: <p>I'm trying to add a clause to an existing GORM criteria to do some extra filtering.</p>
  
  <p>I have various objects in my domain that are owned by an <code>Organisation</code>. For example, <code>Product</code>. Organisations can also own other organisations. I only want <code>User</code>s who belong to a particular <code>Organisation</code> to be able to see domain objects that belong to that <code>Organisation</code> or any of their <code>Organisation</code>'s child <code>Organisation</code>s.</p>
  
  <p>There are a number of places in my application where I need to query for domain objects that are owned by <code>Organisation</code>s. I want to create a <code>DetachedCriteria</code> instance that I can reuse throughout by application wherever I need to filter out objects that a <code>User</code> should not be allowed to see.</p>
  
  <p>I've denormalised my model a bit to hopefully make things easier. When a new <code>Organisation</code> is inserted, it recursively adds itself to the <code>hierarchyOrganisations</code> collection of each of the <code>Organisations</code> above it.</p>
  
  <pre><code>class Organisation {
      ...
      static hasMany = [hierarchyOrganisations: Organisation]
      ...
  }
  </code></pre>
  
  <p>One of the places I want to apply the organisation filtering <code>DetachedCriteria</code> is in Grails' scaffolding controller's <code>list</code> method, so I need to be able to paginate the final set of results.</p>
  
  <p>Using the <code>Product</code> controller's list method as an example, I'm currently doing something like this -</p>
  
  <pre><code>def hierarchyCriteria = new DetachedCriteria(Organisation).build {
      eq('id', currentUserOrganisation.id)
  }
  
  def productInstanceList = Product.createCriteria().list {
      inList('parentOrganisation.id', hierarchyCriteria.list().each { organisation -&gt; 
          organisation.hierarchyOrganisations.collect { it.id }
      })
  }
  </code></pre>
  
  <p>...which works, but I don't think it will scale. I'll be hitting the database to bring back the collection of <code>hierarchyOrganisations</code>, then iterating over them in the application to pull out the id, and then querying again to bring back the final filtered list of organisations.</p>
  
  <p>If I was using SQL, I could use a join or an inner select, but I can't seem to get the syntax right translating this to the GORM criteria dsl. </p>