Grails GORM查询null hasOne关联失败 [英] Grails GORM query for null hasOne association fails

问题描述

我使用Grails 2.2.4（在Grails 2.3.11中有相同的行为）并且有一个引用了域类B的域类A

  class A {
 static hasOne = [b：B] 
 
 static constraints = {b nullable：true} 
} 
 
 class B {
 static belongsTo = [a：A] 
} 
  

我试试找到A有B的所有实例。

  A.findAllByBIsNotNull（）*。b 
  

返回Bs和空列表：

  [null，null，b1，b2，null，...] 
  

？

如果我使用

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

我做错了什么？

更新：

我意识到问题是由于 hasOne 。如果不是 static hasOne = [b：B] ，那么它就是 B b 。前者将外键移到表B，后者在表A中创建外键关系。
那么为什么查询在前一种情况下不起作用，我如何查询所有 A s，当外键在B内时没有 B ？

解决方案

我最终解决了这个问题，感谢@Koloritnij的评论以及@Alexander Suraphel的修改答案。
感谢。

如果外键位于 B 表中（由于 hasOne ），以下两个查询解决了这个问题：

查找所有 A s b s：（ b 不是 null ）：

  A.withCriteria {
b {} 
 } 
  

这会产生一个内连接： SELECT * FROM a IN JOIN b ON a.id = b.a_id;

找到所有 A 没有 B s（ b 是 null $ b

  A.withCriteria {
 createAlias（'b'，'bAlias '，CriteriaSpecification.LEFT_JOIN）
 isNull'bAlias.id'
} 
  

这会导致左外连接： SELECT * FROM a LEFT OUTER JOIN b ON a.id = b.a_id WHERE b.id IS NULL;

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?

解决方案

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;

这篇关于Grails GORM查询null hasOne关联失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！