Hello,

I have this relationship mapped up:
Item <--> Store (Many-to-Many)
Item <-- Brand (Many-to-one, An item can only belong to one brand)
... (and serveral other unrelated mappings to Item)

To create a search, I used this critiera to find all items that belonged to a brand:

However I am having more problems creating a criteria for getting all items that belong to a specific Store (Because of the many-to-many relationship). I could easily do it in HQL:
"... where store in elements( item.stores )"
However i want it to fit in nicely with the Restrictions API because I still want to get all items that belong to a specific brand AND a specific Store.

I was looking at Expression.in( store, item.getStores() ), but as the first argument is a String, something tells me that its not what im looking for.

Any ideas?

Im using Hibernate v3.2.3.ga with Hibernate Annotations (no JPA), MySQL 5.0 and JDK 5.

Regards,
John

Expression.in is a valid restriction in your case if you wanted to make sure that the item brand was in a given store. This is if you were checking that the item is in MANY stores, not just one.

The first argument to the method is the column of which you want to query against, and the second is a array of Ids.


List items = sess.createCriteria(Item.class)
.add( Expression.in( "item.store", new Integer[] { 32, 556, 243 } ) )
.list();

I think you misunderstood me, because there is a many-to-many relationship between Store and Item, item has a List<Store> getStores() method and store has a List<Item> getItems() method.

Given a particular Store, using the criterion API, I want to get all items that has atleast that store in its getStores() list.

The HQL-query would be something like this:

And then just feeding the query a given store



//John

you have to add another "createCriteria" method for associations/collections.


List items = sess.createCriteria(Item.class).
.createCriteria("stores")
.add( Expression.eq( "storeId", storeId) )
.list();



I hope this helps. let me know if i understand the question

Works great! Thanks for your help

//John

Hi,

I am facing the similar problem.

Question on the same line : -

How can I find all the items which belongs to two store? For example I want all the item which belongs to "store1" and "store2"? In my case, there is only two different type of stores available in the system.

I have tried with simple SQL by applying the self join on association table but don't know how can I achieve the same through the Hibernate Criteria?

Appreciate quick answer as I have to meet dead line.