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.