grails - 确定列表是否是另一个列表HQL的子集

Question

我正在尝试为我的Grails应用程序编写查询，该查询选择与Apple实例“相关”的Orange的所有实例。这里的“相关”一词意味着与Banana实例相关联的所有Apple实例与与我们所关注的Cherry实例相关联的Orange实例的某种组合相关联。我已经看过问题here，但是我的查询有点复杂，而且我没有看到如何将给定的答案应用于我的问题。

这是我要处理的类:

class Apple {
    static hasMany = [ bananas: Banana ]
}

class Banana {
}

class Cherry {
    static hasMany = [ bananas: Bannna ]
}

class Orange {
    static hasMany = [ cherries: Cherry ]
}

图片形式如下:

在下面显示的方案1中，所需的查询将仅返回“Apple 1”，因为通过“Cherry”实例的某种组合，与“Apple 2”有关的 Banana的所有实例均与“Orange 1”无关。

在下面显示的方案2中，所需的查询将返回“Apple 1”和“Apple 2”，因为与“Apple 2”相关的所有 Banana实例都通过“Cherry”实例的某些组合与“Orange 1”相关”。

这是我一直在使用的查询:

Apple.executeQuery(
    "SELECT DISTINCT apples
    FROM Apple apples
    INNER JOIN apples.banannas banannas
    WHERE banannas IN(
        SELECT DISTINCT banannas
        FROM Cherry cherries
        INNER JOIN cherries.banannas banannas
        WHERE cherries IN(
            SELECT DISTINCT cherries
            FROM Orange orange
            INNER JOIN orange.cherries cherries
            WHERE orange =:myOrange
        )
    )
    ORDER BY apples.id ASC",
    myOrange: myOrange
)

问题是我的查询针对场景1和2返回“Apple 1”和“Apple 2”。

更新1:

根据要求，这是HQL查询生成的SQL。对不起，果味的 <<< pun

SELECT DISTINCT apple0_.id                     AS id10_,
                apple0_.version                AS version10_,
                apple0_.description            AS descript3_10_,
                apple0_.apple_priority_type_id AS apple4_10_,
                apple0_.apple_status_type_id   AS apple5_10_,
                apple0_.internal_need_date     AS internal6_10_,
                apple0_.name                   AS name10_
FROM   apple apple0_
       INNER JOIN apple_priority_type applepri1_
               ON apple0_.apple_priority_type_id = applepri1_.id
       INNER JOIN apple_status_type applesta2_
               ON apple0_.apple_status_type_id = applesta2_.id
       INNER JOIN apple_banana banana3_
               ON apple0_.id = banana3_.apple_bananas_id
       INNER JOIN banana banana4_
               ON banana3_.banana_id = banana4_.id
WHERE  banana4_.id IN (SELECT DISTINCT banana7_.id
                       FROM   cherry plum5_
                              INNER JOIN cherry_banana banana6_
                                      ON plum5_.id = banana6_.cherry_bananas_id
                              INNER JOIN banana banana7_
                                      ON banana6_.banana_id = banana7_.id
                       WHERE  plum5_.id IN (SELECT DISTINCT plum10_.id
                                            FROM   orange orange8_
                                                   INNER JOIN orange_cherry
                                                              plum9_
                                                           ON
orange8_.id = plum9_.orange_cherrys_id
INNER JOIN cherry plum10_
        ON
plum9_.cherry_id = plum10_.id
WHERE  orange8_.id = 248))
ORDER  BY apple0_.id ASC
LIMIT  100 

更新2:

实际上，我有一种方法可以使用HQL进行此工作，但它并不是一个好用的衬板，就像我在其他地方发现与某个其他类型的实例相关的所有某种类型的衬里一样。这是我的工作范围:

    def bananas= myOrange.cherries.bananas.flatten().unique()
    def apples = Apple.getAll().collectMany{ !it.bananas.isEmpty() && bananas.containsAll( it.bananas ) ? [ it ] : [] }.flatten().unique()
    namedParams.put( "apples", apples )
    if( !apples.isEmpty() ) {
        apples = Apple.executeQuery( "SELECT DISTINCT apples FROM Apple apples WHERE apples IN(:apples) ${additionalQuery} ORDER BY ${sortname} ${sortorder}", namedParams )
    }
    return apples

Answer 1

这是因为，如果您的Apple与香蕉至少有一种联系，它将永远回来。

select
    distinct apple0_.id as id0_,
    apple0_.version as version0_ 
from
    apple apple0_ 
inner join
    apple_banana bananas1_ 
where bananas1_.banana_id in (1,2,3)

您需要做的是从结果中排除不存在 的苹果与您的Cherry相关的香蕉。您将必须查看数据库是否具有oracle minus 之类的功能。

编辑:此查询从列表中删除具有香蕉的苹果不在樱桃香蕉中的苹果。

注意:我没有检查它的性能。

  SELECT DISTINCT apples
    FROM Apple apples
    INNER JOIN apples.bananas bananas
    WHERE bananas IN(
        SELECT DISTINCT bananas
        FROM Cherry cherries
        INNER JOIN cherries.bananas bananas
        WHERE cherries IN(
            SELECT DISTINCT cherries
            FROM Orange orange
            INNER JOIN orange.cherries cherries
            WHERE orange =:myOrange
        )
    )
  and apples not in (
    SELECT DISTINCT apples
      FROM Apple apples
     INNER JOIN apples.bananas bananas
      WHERE bananas NOT IN(
        SELECT DISTINCT bananas
          FROM Cherry cherries
          INNER JOIN cherries.bananas bananas
          WHERE cherries IN(
            SELECT DISTINCT cherries
            FROM Orange orange
            INNER JOIN orange.cherries cherries
            WHERE orange = :myOrange
          )
      )
    )