How to convert a existing SQL to Grails criteria?

Question

Need some help in converting a (complex) existing SQL statement.

1) I have multiple left joins each with multiple AND conditions, the AND conditions are now via restrictions in the generated WHERE clause, so the number of results are less than expected. (The join AND is not equal to a WHERE condition). Question How to add a additional AND in the left join? E.g:

left join cc c 
    on a.w_id = c.w_id
       and a.date >= c.startdate
       and a.date < c.enddate

2) I have several "case when .. as x" lines, How to implement this in grails criteria?

3) Two left joined tables, depending on a field value in the base table, the first or the second table should be joined/give rows.

The first three classes are:

class Appointment{
    static mapping = {
    id          column: 'Id'
    employer    column: 'EMP_ID'
    appDate     column: 'Date'

class Employer {
    static mapping = {
    id          column: 'EMP_ID'
    name        column: 'EMP_NAME'
    }
    static hasMany = [contracts: Contract]

class Contract {
    static mapping = {
    id          column: 'CNT_ID'
    employer    column: 'EMP_ID'
    validFrom   column: 'CNT_STARTDATE'
    validUntil  column: 'CNT_ENDDATE'
    specialContract column: 'SpecialContract'
    }
    static belongsTo = [employer: Employer]

The used criteria (extraction);

        def appointments = Appointment.createCriteria().list {
            createAlias('employer','emp', CriteriaSpecification.LEFT_JOIN)
            createAlias('emp.contracts','cnt', CriteriaSpecification.LEFT_JOIN)
            projections {
            ..
            }

..
// normal existing where conditions
            and {
                ge('appDate', start.clearTime())
                le('appDate', end.clearTime())
            }
            isNull('deleted')
// join conditions, now WHERE conditions, should be LEFT JOIN AND
            or {
                isNull('employer')
                and {
                    eq('cnt.specialContract', true)
                    geProperty('appDate','cnt.validFrom')
                    ltProperty('appDate','cnt.validUntil')
                    ..
                    }
                and {
                    eq('cnt.specialContract', false)
                    geProperty('appDate','cnt.validFrom')
                    ltProperty('appDate','cnt.validUntil')
                    ..
                    }
                }
            order ('date', 'asc')
        }

One of the original sql joins;

left join contract cnt (nolock) 
    on app.emp_id = cnt.emp_id
       and app.appdate >= cnt.cnt_validFrom
       and app.appdate < cnt.cnt_validUntil

Update 2015-09-07

I have now switched to HQL because of the WITH clause, but it is still not working for me.

Native

inner join agenda a on a.id = agr.agendaid
left join contract cnt on agr.wgv_id = cnt.wgv_id and agr.date >= cnt.cnt_validfrom and agr.date < cnt.cnt_validtill

HQL

from Agenda agr inner join agr.agenda a with a.employee.id = :employeeId
left join agr.employer wgr
left join wgr.contracts cnt with agr.date >= cnt.validFrom and agr.date < cnt.ValidUntil

Error and generated SQL with-clause referenced two different from-clause elements [

select ..
from promaetis.Agenda agr
inner join agr.agenda a with a.employee.id = :employeeId
left join agr.employer wgr
left join wgr.contracts cnt with agr.date >= cnt.validFrom and agr.date < cnt.validUntil
where agr.date >= :start
..

Seems that the WITH clause only works correct with a combination of field and parameter, and not with field = field What is the correct syntax for the WITH AND AND clause?

Take a look here http://stackoverflow.com/questions/31621485/grails-subquery-with-join-and-detachedcriteria-keeps-failing/31626811#31626811. You will probably be better off writing it as hql statement which will near enough match your sql statement but with the added benefit of binding in the grails domain objects. — V H, Aug 20 '15 at 21:19
@Victor F: Yesterday there was a different answer, with the createcriteria in the projection section, was this a wrong answer? Please post the previous solution again, maybe I can learn from it. I planned to use it this morning, but it is gone? — fpk, Aug 21 '15 at 05:24
@vahid: Our goal was to remove the sql and replace it with criteria, now you suggest to use HQL, are the benefits exactly the same? — fpk, Aug 21 '15 at 05:27
@vahid, reading the stackoverflow post I still think we need the criteria to do the job, please repost the previous solution. — fpk, Aug 21 '15 at 05:41
solutions / answers can usually only be deleted by person who posted it as well as SO moderators and those above 10k. So unsure of what it was or who posted it. Anyhow I am still curious as to what this will achieve for you as in what the reasons for the change for you are ? http://stackoverflow.com/questions/4401240/hibernate-criteria-vs-hql-which-is-faster http://stackoverflow.com/questions/23025030/hibernate-criteria-vs-named-query If it is complexity you will find most agree hql will be easier to read from above posts. — V H, Aug 21 '15 at 08:32
yes @Peter Kniestedt, but I re-analysed it, and I realized an elementary error. However, I can share it using some SO chat room. — victorf, Aug 21 '15 at 11:41
Basically, I suggested you to include `contract(CriteriaSpecification.LEFT_JOIN) { //comparisons here }` in your `projections{ //... }` section. But, how would you write, for example, `geProperty 'appDate','validFrom'`, if `appDate` is an Appointment property and `validFrom` is a Contract property? — victorf, Aug 21 '15 at 11:45
@vahid, It is difficult for me, as a beginner, to compare both ways, but reading the link you posted I was convinced that the 'criteria' way has more benefits in run-time 'checking' the table field names. But of course I can be wrong. — fpk, Aug 21 '15 at 14:00
I now switch to HQL because of the with clause. But the construction still does not work for me. — fpk, Sep 07 '15 at 05:34

How to convert a existing SQL to Grails criteria?

0 Answers0