How to do a simple table join in Grails

Question

I'm kind of new to grails and I'm having a lot of trouble with joining two existing tables through domain objects that have been created off of those tables. Does anyone know how to do this in grails? Here are what the tables look like and an example of how I need the joined table to look. Thanks in advance for the help.

Table1{ 

     field1table1 
} 

Table2{ 

     field1table2

     field2table2 
} 

I need to join these 2 tables where field1table1 = field1table2 and the resulting table join I need to look like this:

JoinedTable{

     field1table1 

     field2table2 
}

Answer 1

If your domains does not have any relationship (hasOne, hasMany, etc) You can use executequery to execute hql queries something like this :

Table1.executeQuery("select * from Table1 t1,Table2 t2 where t1.field1table1 = t2.field2table2")

Look at doc

Hope this helps

Answer 2

Grails maps associations between domain objects with object references. This uses the table's id column to map the relationship.

For a many-to-many relationship between Table1 and Table2, the typical way to do this in grails is like this:

TableOne {
    static hasMany = [tableOnes: TableOne]
}

TableTwo {
    static belongsTo = TableOne
    static hasMany = [tableTwos: TableTwo]
}

In this case, Grails automatically generates a join table with columns for the ids of each table.

If you need an association joining on non-id columns, you'll have to manage it yourself and join the tables using HQL.