sql - executeQuery in Grails -

i have little bit of problem writing sql queries in groovy (v2.1.7. don't ask, why it's old :) ). i, have 3 tables join, can join them on ids, through third one, contains id pairs. however, create left join, everything, if it's null on right side, should go joined table - problem is, whatever do, null-containg lines don't show up. suspect, it's pair-containg table, don't know sure.

table1 {   id   sth }  table2 {   id   ath }  table3 {   t1id   t2id } 

and in groovy file, table1 has hasmany relation on table2.

static hasmany = [t2s: table2] 

as far saw, have join through these relations.

my sql query looks like:

table1.executequery(    "select t1.sth, t2.ath " +    "from table1 t1 left join t1.t2s t2 " +    "where t1.sth = ......) 

is there other way write sql queries in grails?

try groovy sql object instead amazingly easy:

just add def datasource line under class definition , grails inject datasource or if using groovy may need create datasource. create sql object , use object , return hash map of results.

def datasource  sql sql = new sql(datasource) def rows = sql.rows("select * project name 'gra%'") 

here how create own data source:

def db = [url:'jdbc:hsqldb:mem:testdb', user:'sa', password:'', driver:'org.hsqldb.jdbc.jdbcdriver'] def sql = sql.newinstance(db.url, db.user, db.password, db.driver) 

got of sql object documentation here:

http://docs.groovy-lang.org/latest/html/api/groovy/sql/sql.html