Grails: What is the best way to join unrelated tables?

Question

I know there has to be a GORMy way of doing this.

class A {
  def foo
  C c
}

class B {
  def bar
  C c
}

class C {
  def x
  def y
  def z
}

I want to get all of C where A.foo == 'foo' and where B.bar == 'bar'.

I know I can do it from one or the other parent classes, eg:

def my_cs = A.withCriteria { eq('foo', 'foo') }*.c

...and I could separately grab all my B's where bar == 'bar' and loop through my_cs... but that seems inefficient and I feel like there must be a reasonable way to do it through the criteria syntax.

Is that possible, or is there another acceptable way of doing this?

Thanks.

-------- solution ----------

A.withCriteria {
  createAlias('c.B', 'cb', CriteriaSpecification.LEFT_JOIN)
  eq('foo', 'foo')
  isNull('cb.c')
  projections {
    property 'c'
  }
}

I found that I did not need to test bar for B.

---

Answer 1

If your C class doesn't have a reference back to As of Bs then I think getting the As and Bs separately and then combining them might be your only real option. However, I don't think it's really that bad.

If you want to make sure you have a collection of unique Cs then that's easy, too (no looping required). See http://groovy.codehaus.org/groovy-jdk/java/util/Collection.html#unique%28%29

e.g.

def my_cs = []
my_cs.addAll(A.withCriteria { eq('foo', 'foo') }*.c)
my_cs.addAll(B.withCriteria { eq('bar', 'bar') }*.c)
my_cs.unique(true) // Removes duplicates from the list