many-to-many count without init and component types

puffybsd · **Joined:** Mon Sep 29, 2003 9:39 am **Posts:** 67

I am using the archetypal method of returning the count of a collection without initialization in order to return a total SKU count:

Code:

((Integer)session.filter(review.getSkus(), "SELECT 
COUNT(*)").iterator().next()).intValue();

This is many-to-many, and works beautifully. (tables: review, part, review_part).

Code:

<set name="parts" table="review_part" inverse="false" lazy="true" >
 <key column="review_id"/> 
 <property name="state" type="int" column="state"/>
 <many-to-many class="Part" column="part_id" />
 </set>

Now I would like to return a count of SKU's that have proposed prices which have not been approved yet. To simplify the design, I've added state to the many-to-many review_part table. Instead of drilling threw review->part->price->price_change.state, I can just query the state from the association directly-cleaner design pehaps.

So I create an object called ReviewPart, which consists of a part and a state and belongs to a review, I've changed the mapping to:

Code:

<set name="parts" table="review_part" inverse="false" lazy="true" >
 <key column="review_id"/> 
 <composite-element class="ReviewPart">
  <property name="state" type="int" column="state"/>
  <many-to-one name="part" class="Part" column="part_id" />
 </composite-element>
</set>

And this doesn't work, because:
QueryException: collection of values in filter: this
Hibernate version: 2.1.2
I've read the FAQ that suggests using this technique, and the threads that discuss limitations of the current version. So how do you address this now? Is there a workaround?

puffybsd · **Joined:** Mon Sep 29, 2003 9:39 am **Posts:** 67

Ok, the real question is: why does the FAQ http://www.hibernate.org/118.html#A10 suggest using a composite-element to represent the many-to-many with additional attributes, when it seems it would be more appropriate to say, " just make a 1st class object out of the relationship and use that, because composite-element doesn't support the semantics you'd expect from your plain m-to-m relationship"?

Is it feasible to consider the many-to-many-with-extra-attributes a database implementation detail, and the relationship is better modelled as a one-to-many, where the many is a new class which carries the addtional attribute baggage instead of conceptually assigning the attributes to the relationship itself, they are assigned to the many-class within the context of the parent-one class?

In my case, I have lots of skus. The total set of skus is only important in process of defining the scope of a review. A sku by itself has no state, but in the context of the review, it can be untested, passed, unresolved, rejected or approved. This lends itself to subclassing.

How have others dealt with this? Thanks.

christian · **Posted:** Fri May 21, 2004 2:36 pm

Both approaches are valid, it's a question how you want your object model. If you use a collection of components, the association can only be unidirectional, as the components are completely managed by the entity on one side, the other side can't have references to the same components.

If you use two one-to-many associations and an intermediate entity class, you can make it bidirectional, but at the price of a more complex lifecycle (cascading, etc.).

puffybsd · **Joined:** Mon Sep 29, 2003 9:39 am **Posts:** 67

Christian, Thanks for your response. I'm going to try a joined-subclass, and see how that flies.