CODEWITHSUNDEEP
hibernategrailsgrails-orm
southpaul
southpaul

Reputation: 199

How to do a simple table join in Grails

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 
}

Upvotes: 8

Views: 6926

Answers (3)

Gelberth Amarillo Rojas
Gelberth Amarillo Rojas

Reputation: 679

You can use a join sentence like this, this worked for me withough any relationship configuration between tables

def result = Table1.executeQuery("select t1 from Table1 t1 left join Table2 t2 on t1.fieldtable1 = t2.fieldtable2")

Hope this helps

Upvotes: 0

ataylor
ataylor

Reputation: 66059

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.

Upvotes: 1

Alidad
Alidad

Reputation: 5538

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

Upvotes: 6

Related Questions