1

I have following domain structure :-

class A{
  B b
  C c
  String email
}

class B{}

class C {
  D d
}

class D{}

Below is my SQL query which I needed to convert into Grails Criteria Query.

select * from A aa  
  inner join C ca on ca.c_id = aa.id and aa.email = 'source'
  inner join D da on da.id = ca.d_id
  inner join B ba on aa.b_id = ba.id
  inner join A aa1 on b.id = aa1.b_id and aa1.email = 'response'
Sumit Verma
  • 91
  • 1
  • 7

2 Answers2

0

in order to implement the query you want with hibernate/GORM you must extend your domain classes a bit:

class A{
  B b
  C c
  String email
  A a // self-ref
}

then your query should look like:

A.withCriteria{
  eq 'email', 'source'
  a{ 
    eq 'email', 'response'
  }
}
injecteer
  • 20,038
  • 4
  • 45
  • 89
0

I have renamed the tables to something that makes sense like: 

class Apple{
  Ball ball
  Cat cat
  String email
}

class Ball{}

class Cat {
  Dog dog
}

class Dog{}

And the create criteria is:

return Apple.createCriteria.list() {

    and {
        and {
            eq("email", "source")
            cat
        }
        and {
            cat {
                dog
            }
        }
        and {
            eq("email", "response")
            ball
        }
    }
}

If you have a real data and let me know if you need any help.

You can also look at this example.

biniam
  • 8,099
  • 9
  • 49
  • 58
  • @ Biniam if I add the following it make result list size 0. and { eq("email", "response") Ball {} } – Sumit Verma May 29 '15 at 12:22
  • @SumitVerma I have changed the code. Please test it now. (I removed the {} after Ball and Dog and renamed Ball to ball and Dog to dog - referring to the object not the class) – biniam Jun 04 '15 at 08:51