CODEWITHSUNDEEP
hibernategrailsgrails-ormgrails-2.0
Ashish Joseph
Ashish Joseph

Reputation: 1153

Grails/Hibernate - Criteria to fetch a master by two child records

For the domain classes below.

class Author {
    String name;
    static hasMany=[books:Book]
}

class Book {
    int serialNumber;
    String name;
}

I would like to get the Author whose,

  1. first(serialNumber=1) book is "ABC"(name) AND
  2. second(serialNumber=2) book is "XYZ"(name)

Even though there are data matching the condition, The below code doesn't work

def c = Author.createCriteria()
def results = c.list {
    books {
        and {
            and {
                eq("serialNumber", 1)
                eq("name", 'ABC')
            }
            and {
                eq("serialNumber", 2)
                eq("name", 'XYZ')
            }
        }
    }
}

I have only a basic knowledge of SQL, so if you are suggesting any SQL based solution, please guide me to the right direction.

Upvotes: 0

Views: 378

Answers (3)

user2560528
user2560528

Reputation: 105

use "inList"

Collection<Author> authors = Author.createCriteria().list {
  books {
     inList('serialNumber', [1L, 2L])
     inList('name', ['A', 'B'])
  }
}

Upvotes: 0

Gerardo Lastra
Gerardo Lastra

Reputation: 665

First of all, those and's are superfluous, since your code is equivalent to

def c = Author.createCriteria()
def results = c.list {
    books {
        eq("serialNumber", 1)
        eq("name", 'ABC')
        eq("serialNumber", 2)
        eq("name", 'XYZ')
    }
}

This is easier to understand and it means "find the Authors that own a book whose serial number is 1 and 2 at the same time and whose name is ABC and XYZ at the same time" and of course will not return anything.

I doubt your model allows for such queries using criteria, so I would advise to use HQL or other means.

Upvotes: 2

Victor Sergienko
Victor Sergienko

Reputation: 13485

I'm afraid (not 100% sure) Criteria syntax can only query a single child record, so you'll have to use HQL like:

from Author as a
where exists (
  from Book as b1 where b1.author = a and b1.serialNumber = 1 and b1.name = 'ABC'
)
and exists (
  from Book as b2 where b2.author = a and b2.serialNumber = 2 and b1.name = 'XYZ'
)

Maybe you can replace from Book as b2 where b2.author = a with from a.books as b2 - I cannot test it now.

Upvotes: 3

Related Questions