Grails GORM query for null hasOne association fail

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

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

Answer 3

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 4

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

Or

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