0

How do i write below query in Grails either using criteria query or executeQuery?

select * from table1 as t1 left  Join(select * from table2 where id=2)as t2 On  t2.table1=t1.id ;
Ankit Tater
  • 599
  • 3
  • 9
  • 26

2 Answers2

0

Unlike SQL, in which you query tables, GORM/Hibernate queries query domain classes. Yes, it boils down to tables, but from the Criteria, WHERE, and HQL point-of-view, it's domain classes. Which is why, as Koloritnij pointed out, knowledge of the domain model is necessary for writing the query.

SQL vs GORM

One difference in how SQL and GORM perform joins is that SQL joins are created on-the-fly, in the SQL itself. Whereas GORM joins are predetermined by the domain class associations. This means that in HQL you cannot join to a sub-query. You can read more about such differences here.

An example

That being said, using your example SQL I made an assumption about your domain model:

class DomainA {
}

class DomainB {
    DomainA a
}

In the domain model above, DomainB (table2) has a uni-directional many-to-one association with DomainA (table1). An HQL similar to your SQL is as follows:

def hql = 'SELECT a, b FROM DomainB AS b RIGHT OUTER JOIN b.a AS a WHERE b.id = :id'

The HQL can be executed like this:

def result = DomainB.executeQuery(hql, [id: 2])
Emmanuel Rosa
  • 9,697
  • 2
  • 14
  • 20
  • Basically i want to perform join on result of subquery, if i do the way u did then where clause will be performed on the result of join, I want to use where condition on both table and then want to join there reusult, something like union. – Ankit Tater Dec 30 '15 at 04:52
  • Well, unfortunately GORM/Hibernate does not support joining on sub-queries. Unions are quite different than what you demonstrated, and also not supported. – Emmanuel Rosa Dec 30 '15 at 05:00
0

Here you go (if you have Domain Classes with foreign key): 

def query
query = sessionFactory.getCurrentSession().createCriteria(table2.class)
query = query.createAlias("table1", "table1Alias",  CriteriaSpecification.LEFT_JOIN, Restrictions.in( 'table1Alias.id', 'table2.id'))
Restrictions.eq( "id", 2)

If not, you have to use raw SQL: 

def dataSource
Sql sql = new Sql(dataSource)
sql.exequteQuery("""....""")
Raz Abramov
  • 181
  • 12