Databases
StackOverflow2013
Postsdbo
POHibernate many-to-many: Criteria for looking up all class A which DO NOT contain class B
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHibernate many-to-many: Criteria for looking up all class A which DO NOT contain class B
    text
    copied!<p>I have a many-to-many relationship between Item and ItemCategory. For an Item, I want to know what categories the Item is not in. I also want to know for an ItemCategory, which items the category does not have. The SQL for the second one should be something like this:</p> <pre><code>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 = ?) </code></pre> <p>I'm using DetachedCriteria because I'm including this as part of an ad-hoc query. I think I'm close to a solution, but it seems like Hibernate is not generating the correct solution. My code has this:</p> <pre><code>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)); </code></pre> <p>The generated SQL looks like this: </p> <pre><code>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=?) </code></pre> <p>Note that it's missing the join table (called "item_categories"). How do I fix this?</p> <p>More info: here's the Hibernate mapping of "ItemCategory.items"</p> <pre><code>&lt;set name="items" table="item_categories" lazy="true" inverse="true" cascade="none" sort="unsorted"&gt; &lt;cache usage="nonstrict-read-write"/&gt; &lt;key column="ITEM_CATEGORY_ID"&gt; &lt;/key&gt; &lt;many-to-many class="com.dr_dee_sw.wine.dto.Item" column="ITEM_ID" outer-join="auto"/&gt; &lt;/set&gt; </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload