Gorm Hibernate criteria subqueries with exists restriction

In this post I’ll show how to use the detached criteria to execute subqueries within a criteria query. 

Sub queries and detached criteria are very powerful but often neglected by the developers. I think that is mainly because many developers are not familiar with it and doesn’t know how to take advantage of it. However, when used properly, the DetachedCriteria can be quite powerful and provieds many advantages. It can be used to create subqueries, common reusable queries and to perform bulk operations.

One very common requirement while working with Gorm/Hibernate or ORM in general is, to check, if some related objects exists. In sql, this is generally done using an exists condition with a subquery. Criteria queries also supports the exists restriction which accepts a detached criteria as a subquery.

Example of a criteria query with exists and subquery

The Domain Model

    class Author {
      String name
    }
    
    class Book {
       String name
       Author author
       Date datePublished
    }
    
    class ReadingList {
    	String user
    	static hasMany = [books:Book]
    }

This is an unidirectional one to many relation between author and book.

Let’s say we want to find an author who has a book published in year 2017. We can do this with either a join or with an exists query. However because author domain does not have a reference to book, so join is not possible when using criteria. So ther other option is to use an exists.

It can be done with following sql query.

select * from author author where exists (select 1 from book b where b.authorId = author.id and year(b.publishedDate) = 2017

 Example with criteria

    		DetachedCriteria subQuery = Book.where({
    			year("datePublished") == 2016
    			eqProperty("author.id", "this.id")
    			setAlias "book"
    
    			projections {
    				property "id"
    			}
    		})
    
    		List<Author> authors = Author.createCriteria().list {
    			exists(subQuery)
    		}

The above criteria will generate the following sql query

    select this_.id as id1_0_0_, this_.version as version2_0_0_, this_.name as name3_0_0_ from author this_ 
    where exists (select book_.id as y0_ from book book_ where book_.author_id=this_.id)

Notice the setAlias("book") in subquery. It is necessary, without it, hibernate will generate a wrong sql query. Also see how the author.id is compared with book.authorId using eqProperty("author.id", "this.id")

Below is a complete test for it.

    import grails.gorm.DetachedCriteria
    import grails.test.mixin.integration.Integration
    import grails.transaction.Rollback
    import spock.lang.Specification
    
    @Integration
    @Rollback
    class CriteriaWithSubquerySpec extends Specification {
    
    	def "test find authors who has written a book in specified year"() {
    		given:
    		Author author = new Author(name: "Author-1").save()
    	
    		Date d2017 = Date.parse("yyyy-MM-dd", "2017-01-01")
    
    		Book book = new Book(name:"Book", author:author,datePublished: new Date()).save()
    		Book book1 = new Book(name:"Book-1", author:author,datePublished: d2017).save()
    
    
    		when: "Find all authors who has published book in 2017"
    		DetachedCriteria subQuery = Book.where({
    			year("datePublished") == 2017
    			eqProperty("author.id", "this.id") //here "this" refers to the root alias of criteria
    			setAlias "book"
    
    			projections {
    				property "id"
    			}
    		})
    
    		List<Author> authors = Author.createCriteria().list {
    			exists(subQuery)
    		}
    
    		then:
    		noExceptionThrown()
    		authors.size() == 1
    		authors.contains(author)
    	}

Note: Here I have used the grails where query to create subquery. Grails Where queries are actually detached criteria builder. and can be used anywhere where a detached criteria can be used.

Lets see how to find all books which are included in any reading list.

    void "test: findBooks which are in readinglist"() {
    		Author author = new Author(name: "Author-1").save()
    		Date now = new Date()
    
    		Book book = new Book(name:"Book", author:author,datePublished: now).save()
    		Book book1 = new Book(name:"Book-1", author:author,datePublished: now).save()
    		Book book2 = new Book(name:"Book-2", author:author,datePublished: now).save()
    
    		ReadingList readingList = new ReadingList(user: "User-1")
    		readingList.addToBooks(book)
    		readingList.addToBooks(book1)
    
    		readingList.save(flush:true)
    
    		when:
    		DetachedCriteria subQuery = new DetachedCriteria(ReadingList).build {
    			books {
    				eqProperty("id", "this.id") //here this: refers to the root alias of the criteria query
    			}
    
    			setAlias("readingList")
    		}
    
    		List books = Book.createCriteria().list {
    			exists(subQuery.id())
    		}
    
    		then:
    		books.size() == 2
    		books.containsAll([book, book1])
    
    	}

I have used DetachedCriteria(ReadingList).build {} in above example, but you can use a where query too.

Note just exists, criteria supports subqueries for many other restrictions too. eg. in, notin, inlist, gtAll etc.