title: Transform HQL into Criteria
body: <p>I have a query in HQL which fully works. However, I'd like to have it expressed in the Criteria form, which is easier to read and maintain.</p>
  
  <p>This is the scenario: I have Workers, People and Payments. A worker is a Person plus a Job Type (one person, in this app, can be two workers with distinct Job Types at the same time). There are many Payments, and there might be more than one for one worker. I need to get each worker one time, and then the sum of all the Payment values for that worker. These are the HQL Queries:</p>
  
  <p>1- Get all payments:</p>
  
  <pre><code>Payment.findAll("from Payment as p where p.month = :m and p.year = :y group by p.worker.id, p.worker.person.id", [m: paymentsMonth, y: paymentsYear])
  </code></pre>
  
  <p>2- Iterating through the Payments, for each one I get the sum of the Payments for that worker this way (it is the payment):</p>
  
  <pre><code>def totalLiquidValue = Payment.executeQuery('''select sum(liquidValue) from Payment where
                                                  month = :m and
                                                  year = :ar and
                                                  worker = :w''',
                                                  [m: it.paymentMonth, ar: it.year, w: payment.worker])
                                                  .first()
  </code></pre>
  
  <p>It works, but not the Criteria I tried to write to replace it:</p>
  
  <p>1- </p>
  
  <pre><code>def payments = Payment.withCriteria {
                      worker {
  
                          projections {
                              groupProperty('jobType')
                          }
  
                          person {
                              projections {
                                  groupProperty('id')
                              }
                          }
                      }
  
                      eq('month', paymentsMonth)
                      eq('year', paymentsYear)
                  }
  </code></pre>
  
  <p>It fails with "com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'person_ali2_.msid' in 'field list'"</p>
  
  <p>'msid' is actually the name of the 'id' field in the database. That's done via mapping id in the domain class Worker.</p>
  
  <p>Do you see any new possibilities?</p>
  
  <p>Thanks</p>
  
  <p>Update:</p>
  
  <p>We need to use legacy databases, provided by the client. For better organization, the classes we use from them are represented as views, named "legacy_tablename", and everything that is exclusively inside our part of the system is in a separate database, so the views reference tables in other databases and we don't need to treat multiple databases inside the code. So that's why sometimes table names are specified.</p>
  
  <p>The domain classes are big, but these are the important details about them:</p>
  
  <pre><code>class Payment{
  
      Integer year
      Integer month
  
      Worker worker
  
      BigDecimal liquidValue
  
  }
  
  class Worker {
      Person person
      Integer jobType
  
      static mapping = {
        table("legacy_worker")
        id(composite: ["jobType", "person"])
        person(column: "msid", fetch: "join")
      }     
  }
  
  class Person {
      String id
  
      static mapping = {
         table("legacy_person")
         id(column:"msid", generator: "assigned")
      }
  }
  </code></pre>
  
  <p>Thanks</p>