title: Grails Criteria HQL Many-to-Many with group by
body: <p>I have two domains related through a many-to-many relationship, <strong>Project</strong> and <strong>ProjectCategory</strong>, where projects can have many categories, and one category might be assigned to several projects. So:</p>
  
  <pre><code>class ProjectCategory {
     String name
     ...
  }
  
  class Project {
     static hasMany = [categories : ProjectCategory]
     ...
  }
  </code></pre>
  
  <p>I want to count how many projects exists for each category. The <strong>SQL</strong> to accomplish that is quite simple:</p>
  
  <pre><code>select cat.name category, count(pcat.project_categories_id ) projCount
    from project_project_category pcat, 
         project_category cat
   where pcat.project_category_id = cat.id
   group by project_category_id
  </code></pre>
  
  <p>Which returns something like this:</p>
  
  <pre><code>category    |  projCount
  'cat_1'     |  3
  'cat_2'     |  4
  'cat_3'     |  1
  ...
  </code></pre>
  
  <p>Now, the question is, how to do this using Criteria or HQL, something which looks more "Grails/Groovy style"?</p>
  
  <p>Thanks in advance.</p>
  
  <p><strong>EDIT</strong></p>
  
  <p><em>I've managed to resolve in <strong>HQL</strong>:</em></p>
  
  <pre><code>def result  = ProjectCategory.executeQuery(
      ''' select c.name, count(p.id)
            from Project p join p.categories c
           group by c ''' )
  </code></pre>