Grails 2.4.4 executeQuery() to join tables from a table of a database

Question

this is the code for my domains.

class Btr {

    Date dateBreak
    int timeBreak
    String typeBreak
    User usuario

    static constraints = {
    }

    static mapping = {

      }
}


class User {

    String name
    String user
    String password
    String confirmPassword
    String state
    String extent
    String movileNumber
    String email
    String address
    Rol rol



    static constraints = {

    }

    static mapping = {

    }
}

This is the code for my controller.

def df = new SimpleDateFormat("yyyy-MM-dd HH:mm")
def startDate = params.startDate
def stopDate = params.stopDate

resultado = Btr .executeQuery("select dateBreak, timeBreak, typeBreak, 
user, usuario.rol from Btr inner join User on user = usuario.rol where 
dateBreak between :startDate" and :stopDate", [startDate: 
df.parse(startDate), stopDate: df.parse(stopDate)])


render (view: "data", model: [result: resultado])

This is my view.

  <g:each in="${result}" var="results" status="i">
    <tr><td>{results.dateBreak}</td><td>{results.timeBreak}</td><td>
    {results.typeBreak} </td><td>${results.usuario.rol}</td></tr>
    </g:each>

Then i get this error when i submit the form. in the GSP, when i am printing data,

Exception evaluating property 'dateBreak' for java.util.Arrays$ArrayList, Reason: groovy.lang.MissingPropertyException: No such property: dateBreak for class: java.sql.Timestamp

could someone please tell me how to join tables in grails with executeQuery and also would be nice to learn to do it with, withCriteria

score 0 · Answer 1 · answered Apr 13 '17 at 13:26

resultado = Btr .executeQuery("select dateBreak, timeBreak, typeBreak, 
user, usuario.rol from Btr inner join User on user = usuario.rol where 
dateBreak between :startDate" and :stopDate", [startDate: 
df.parse(startDate), stopDate: df.parse(stopDate)])

Should be

resultado = Btr .executeQuery("""select new map (btr.dateBreak as dateBreak, btr.timeBreak as timeBreak, btr.typeBreak as typeBreak, 
u as user, user.usuario.rol  as rol) from Btr btr join btr.user u where 
btr.dateBreak between :startDate and :stopDate""", [startDate: 
df.parse(startDate), stopDate: df.parse(stopDate)])

what you have is raw sql and not HQL which is a slight variation and uses actual domain objects to join

Use left join for hasMany where it may be null join for typical one to one relationship

Also use left join if one to one relationship can be null

Beyond that you could have put your actual query as a raw sql query like so

def sql=new Sql(dataSource)
return sql.rows(query,whereParams)

Grails 2.4.4 executeQuery() to join tables from a table of a database

1 Answers1