18 November 2009

Ways to map and query many-to-many in GORM

I played with many-to-many relations in grails and found some interesting ways to work with them.
First the domain definitions.
For the purpose of this demo I chose the classic example with books and authors. A book may have many authors and an author may have many books.

class Author {
String name
}

class Book {
String name
}

What I consider the silliest way to define your domains I found here http://www.grails.org/Many-to-Many+Mapping+without+Hibernate+XML
They suggest mapping the join table using a domain object and creating the links by hand.
On 'books and authors' it would look like

class Book {
String name
static hasMany = [ memberships: Membership]

List authors() {
return memberships.collect {it.author}
}

List addToAuthors(Author author) {
Membership.link(this, author)
return authors()
}

List removeFromAuthors(Author author) {
Membership.unlink(this, author)
return authors()
}
}

class Author {
String name
static hasMany = [memberships: Membership]

List books() {
return memberships.collect {it.book}
}

List addToBooks(Book book) {
Membership.link(book, this)
return books()
}

List removeFromBooks(Book book) {
Membership.unlink(book, this)
return books()
}
}

class Membership {
Book book
Author author
static Membership link(book, author) {
def m = Membership.findByBookAndAuthor(book, author)
if (!m) {
m = new Membership()
book?.addToMemberships(m)
author?.addToMemberships(m)
m.save()
}
return m
}

static void unlink(book, author) {
def m = Membership.findByBookAndAuthor(book, author)
if (m) {
book?.removeFromMemberships(m)
author?.removeFromMemberships(m)
m.delete()
}
}
}

While this works it doesn't seam very elegant to me.
So, moving on, I found a better way to do it.

class Author {
String name

static hasMany = [books:Book]
}

class Book {
String name

static belongsTo = Author
static hasMany = [authors: Author]
}

This seems the best way. Or you could use hibernate annotations, but I won't treat that.
Now let's see how we can query these.
Let's say I want to get all the books written by an author.
First dynamic finders. This is done in 2 steps:

def author = Author.findByName(a_Name)
def books = author ? Book.findAllByAuthor(author) : []

With Hql it would look something like:

Book.executeQuery("from Book as b join b.authors a where a.name = :name order by b.name", [name: a_Name, max: max, offset: offset])
Book.findAll("from Book as b join b.authors a where a.name = :name order by b.name desc", [name: a_Name, max: max, offset: offset]);

This returns a list of arrays containing book and author
To get only the books use:

Book.executeQuery("select b from Book as b join b.authors a where a.name = :name order by b.name", [name: a_Name, max: max, offset: offset])

Finally, using criteria:

def criteria = Book.createCriteria()
def books = criteria.list {
authors {
eq('name', a_Name)
}
maxResults(max)
firstResult(offset)
order("name", "asc")
}

So these are some ways to query many-to-many relations with gorm, also using paging.
Hope it was helpful.

3 comments:

  1. Well well...where was this article when I needed it, Gabi? :P

    Good stuff, beginning grails and many to many gave me some tough times. But it seems they're fairly straight forward. Thanks a lot!

    ReplyDelete
  2. Very nice article,

    But I'm still trying to decide which is better,

    Another suggested method is to remove the hasMany altogether, This greatly reduces the SQL generated and loading of the list
    check it out,

    http://burtbeckwith.com/blog/?p=191

    ReplyDelete