java - Hibernate多对多: Criteria for looking up all class A which DO NOT contain class B

Question

我在 Item 和 ItemCategory 之间存在多对多关系。对于一个 Item，我想知道该 Item 不属于哪些类别。对于一个 ItemCategory，我还想知道该类别不包含哪些项目。第二个的 SQL 应该是这样的:

SELECT item 
FROM Item AS item 
WHERE item.id NOT IN (SELECT item2.id FROM Item AS item2 LEFT JOIN item2.categories AS cat WHERE cat.id = ?)

我使用 DetachedCriteria 是因为我将其作为临时查询的一部分。我认为我已经接近解决方案，但 Hibernate 似乎没有生成正确的解决方案。我的代码是这样的:

DetachedCriteria subquery = DetachedCriteria.forClass(Item.class, "item");
DetachedCriteria catSubquery = subquery.createCriteria("categories", "cat");
SimpleExpression criterion = Restrictions.eq("id", value);

catSubquery.add(criterion);
criteria.add(Subqueries.propertyNotIn("id", subquery));

生成的 SQL 如下所示:

select ... 
from wine.categories this_ 
where this_.CATEGORY_ID not in 
  (select this0__.CATEGORY_ID as y0_ 
   from wine.categories this0__ 
   where item1_.INVENTORY_ITEM_ID=?)

请注意，它缺少连接表(称为“item_categories”)。我该如何解决这个问题？

更多信息:这是“ItemCategory.items”的 Hibernate 映射

<set name="items" table="item_categories" lazy="true" inverse="true"
  cascade="none" sort="unsorted">
  <cache usage="nonstrict-read-write"/>

  <key column="ITEM_CATEGORY_ID">
  </key>

  <many-to-many class="com.dr_dee_sw.wine.dto.Item" column="ITEM_ID"
    outer-join="auto"/>

</set>

Answer 1

// get all items which are not in the specified category

List<Item> itemsNotInCategory = DetachedCriteria.forClass(Item.class)
    .add(Subquery.propertyNotIn("id", DetachedCriteria.forClass(Category.class)
        .add(Restrictions.eq("id", catid))
        .createAlias("items", "item")
        .setProjections(Projections.Distinct(Projections.Property("item.id")))
    ))
    .List();