Grails GORM query for null hasOne association fails

Question

I use Grails 2.2.4 (same behaviour in Grails 2.3.11) and have a domain class A that references a domain class B

class A {
    static hasOne = [b: B]

    static constraints = { b nullable: true }
}

class B {
    static belongsTo = [a: A]
}

I try to find all instances of A that have a B.

A.findAllByBIsNotNull()*.b

returns a list of Bs and nulls:

[null, null, b1, b2, null, ...]

How so?

Same happens if I use

A.withCriteria {
    isNotNull 'b'
}*.b

What do I do wrong?

UPDATE:

I realized that the problem is because of the hasOne. If instead of static hasOne = [b: B], there is B b, it works. The former moves the foreign key to table B, the latter creates the foreign key relation in table A. So why does the querying not work in the former case and how can I query for all As, not having a B when the foreign key is within B?

Homogenous

what happens if you call println A.findAllByBIsNotNull()*.b*.id ? – Frisse

Answer 1

I finally solved it, thanks to the comment of @Koloritnij and the modified answer of @Alexander Suraphel. Thanks for that.

If the foreign key is on the B table (due to the hasOne), the following two queries solve the case:

finding all As with Bs: (b is not null):

A.withCriteria {
  b {}
}

This results in an inner join: SELECT * FROM a INNER JOIN b ON a.id=b.a_id;

finding all As without Bs (b is null):

A.withCriteria {
  createAlias('b', 'bAlias', CriteriaSpecification.LEFT_JOIN)
  isNull 'bAlias.id'
}

This results in a left outer join: SELECT * FROM a LEFT OUTER JOIN b ON a.id=b.a_id WHERE b.id IS NULL;

Homogenous

.+1 for answering your question! Consider upvoting and/or accepting the other answers. – Melchior

Answer 2

Following UPDATE:

Use one of the fields in B. Let's say B has the field name:

A.withCriteria { b { isNotNull("name") }}*.b

Old Answer:

The problem could be in the toString() of Class B.

Add

String toString() { 
    getClass().name
}

to Class B and try running your query again.

Melchior

Very creative, but no. Java implements default toString in a proper way. And Groovy does not change that. – Homogenous

Answer 3

Also for future reference, anyone looking to do the same but with GORM Where queries or DetachedCriteria, the equivalent would be as follows:

def criteria = new DetachedCriteria(A)
criteria.where {
    join('b', JoinType.LEFT)
    b {
        isNull 'id'
    }
}.list()

Halland

Answer 4

If u need only to take B, why u can't use:

B.findAll()

or if by A:

 B.findAllByA(a)

UPD: Use criteria:

    A.createCriteria().list{
       isNotNull 'B'
       isNull 'C'
    }

Or this is bad idea but must work:

def bList = B.createCriteria().list{
    projections{
       property 'id'
    }
    A.createCriteria().list{
      b{
       'in' ('id',bList)
      }
    }

Prevocalic

The example is a simplified case. What I actually want to is A.findAllByBIsNotNullAndCIsNull() and then go through all the As. – Homogenous

A.createCriteria().list { isNotNull 'b' }*.b returns [null, null, null, ...]. – Homogenous

A.createCriterya().list{b{isNotNull 'id'}}*.b this is my last idea – Prevocalic

That indeed solves it! It results in an inner join of A and B and thus only returns A with a B. Unfortunately, there is no way to query for all As without a B. I need to do this as well... – Homogenous

Answer 5

Shouldn't that be A.findAllBybIsNotNull()*.b (note lower case b) ?

Or

A.findAll("from A as a where a.b is null")

Tonsillotomy

B should be upper case (see examples in grails.org/doc/2.2.4/guide/GORM.html#finders, e.g. String title -> findByTitle...) A.findAll("from A as a where a.b is null")*.b returns an empty list. A.findAll("from A as a where a.b is not null")*.b returns a list with nulls and non-nulls. – Homogenous