Transform HQL into Criteria

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.

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:

1- Get all payments:

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])

2- Iterating through the Payments, for each one I get the sum of the Payments for that worker this way (it is the payment):

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()

It works, but not the Criteria I tried to write to replace it:

1-

def payments = Payment.withCriteria {
                    worker {

                        projections {
                            groupProperty('jobType')
                        }

                        person {
                            projections {
                                groupProperty('id')
                            }
                        }
                    }

                    eq('month', paymentsMonth)
                    eq('year', paymentsYear)
                }

It fails with "com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'person_ali2_.msid' in 'field list'"

'msid' is actually the name of the 'id' field in the database. That's done via mapping id in the domain class Worker.

Do you see any new possibilities?

开发者_运维问答

Thanks

Update:

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.

The domain classes are big, but these are the important details about them:

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")
    }
}

Thanks

---

      def payments = Payment.withCriteria {
           createAlias('worker','w')
           projections {
               property('w.id')  
               groupProperty('w.jobType')
               groupProperty('w.person')                       
            }
            eq('month', paymentsMonth)
            eq('year', paymentsYear)
        }

Above query would give you list of lists, where 1st element would be worker, 2nd would be jobType and 3rd would be person object which has given month and year.

  def payments = Payment.withCriteria {
            projections {
             sum('liquidValue')
            }   
            eq('month', paymentsMonth)
            eq('year', paymentsYear)
            eq('worker',worker)
        }