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?

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;

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.

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()