grails - 如何在子查询中实现计数

Question

我有以下表格结构:

TABLE A:
ID
COL1
COL2
...
COL(n)

TABLE B:
ID
A_ID (id in table A)
VALUE

A-> B存在一对多关系

class A {
    int id
    ...
    coln
    Set<String> bSet

    static hasMany = [bSet: B]

    static mapping = {
        restrictions joinTable: [name: "B", key: "A_ID", column: "VALUE"]
    }
}

如何建立条件，使其执行如下查询:

select table1.* from A table1 where (select count(*) from B table2 where table2.A_ID = table1.ID and table2.VALUE in ('excluded_value_1','excluded_value_2')) = 0

Answer 1

试试看，显然未经测试，但可能会给您一些起点

def a = A.createCriteria()
a.list {
 createAlias("bSet", "b", CriteriaSpecification.LEFT_JOIN)
 not {
    'in'("b",['excluded_value_1','excluded_value_2'])
 }
}