1

I am hung up on how to handle the IFNULL() in both the select and where clauses of this.

I know that HQL provides COALESCE() instead but I'm not sure how to proceed w.r.t. criteria.

sql.eachRow("""SELECT DISTINCT cl.name, b.title, b.type, cb.region, IFNULL(cb.start, c.start),IFNULL(cb.end, c.end)
    FROM table.C_B cb
    JOIN table.B b on b.id = cb.B_id
    JOIN table.C c on c.id = cb.C_id
    JOIN table.CL cl on cl.id = c.CL_id
    WHERE cb.status='active' AND
    DATEDIFF(IFNULL(cb.start, c.start),'""" + startmonth + """') <=0
    AND DATEDIFF(IFNULL(cb.end, c.end),'""" + startmonth + """') >0 order by cl.name, b.title"""
) {row ->
    str.append('<tr>')
    (0..3).each {str.append('<td>' + row[it] + '</td>')}
    (4..5).each {str.append('<td>' + String.format('%tY/%<tm/%<td', row[it]) + '</td>')}
    str.append('</tr>')
}

Is sqlProjection the way forward?

Bob Brown
  • 930
  • 2
  • 7
  • 14

1 Answers1

0

My suggestion would be don't. GORM is good for simple queries and CRUD, however is permissible and sometimes necessary to drop down to straight SQL. HQL can get you closer, because you could create your own dialect, extended from the one you're using and adding functions you are missing.

Where queries, Criteria queries, HQL,SQL, are all tools at your disposal. Towards the former is easier to code and maintain, for CRUD and simple queries, but may lack functionality, or be non performant for certain scenarios. Towards the latter, would be easier to write, and tweak for complex, and performance scenarios. So in short you'll have to find a balance of when to use the right tool for the job.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
virtualdogbert
  • 462
  • 3
  • 12
  • All of IT is about finding that balance, isn't it. I confess to always wanting to find ways to avoid string slinging whenever I can. – Bob Brown Jun 30 '20 at 10:54
  • Very true, I find once you go past, a few joins, or are trying to use a bunch of special functions, dropping to HQL, then if need be SQL is the way to go. for adding custom functions look at the answers on this post: https://stackoverflow.com/questions/35930600/how-to-use-native-sql-function-with-hql-query and this blog post: https://blog.andresteingress.com/2013/10/23/grails-adding-mysql-collation-support-to-gorm.html – virtualdogbert Jul 01 '20 at 11:24