Query based on contents of collection

moebius · **Posted:** Tue Nov 07, 2006 9:23 am

I'm not 100% sure how to ask this question so searching the forums and goolge haven't been very helpful. Here is my situation:

I have a many-to-many relationship between Parts and Categories (a part may belong to many categories and a category may describe many parts). This is fine, and all works well.

What I am wanting to do is filter the parts based on whether they belong to all categories of a selected list (ie the user selects multiple categories and the program finds the parts that fit into every category).

I have been trying along the lines of:

Code:

from Part as part where (? in (select elements(AircraftModels) from part) and ? in (select elements(AircraftModels) from part) and......)

with each category in the list. Not only does it not work, it is incredibly ugly and I don't want to use it!! There must be a better way, however I cannot see anything in the manual that jumps out as being on the right track.

Any suggestions?

anuarneto · **Joined:** Fri Oct 20, 2006 8:02 am **Posts:** 36

If I understand correct, it should work:

Code:

select elements(C.Parts) from Category C where C.Id in (<previous selected ids>)

[/code]

moebius · **Posted:** Tue Nov 07, 2006 5:40 pm

Edit:Duplicate post sorry

moebius · **Posted:** Tue Nov 07, 2006 5:41 pm

anuarneto: Thanks - not quite what I was after, but I think I'm nearing the right track.

This is what I have now:

Code:

"select distinct part from Part as part where part.PartCategories = any indices(:categories)"

It's throwing a QueryException: "unindexed collection before []" though.

I think it might be something to do with the way the collections are set up - I'm using IList<> in code, and bag in .hbm.xml.
I just read in the manual that the bag is unindexed and unordered etc, so I've tried to change it to other things, like set and list. All of these throw errors.

Which pair of things (collection in code and collection in .hbm.xml) should I use that are indexed AND handle many-to-many?

Thanks

mnichols · **Joined:** Mon Aug 21, 2006 9:18 am **Posts:** 179

Hi,
You might want to try a <list/> mapping collection that maps to an IList<T>.

Here's a snippet of how I use it for an address object:

Code:

 <list name="StreetFields" cascade="all-delete-orphan" table="StreetField" >      
      <key column="StreetAddressId" foreign-key="StreetAddress_StreetField"/>
      <index column="Position" />      
      <one-to-many class="Cei.eMerge.Core.Domain.Contacts.StreetField, Cei.eMerge.Core" />
    </list>

Within HQL I can reference fields according to their index ('Position') .

If you are using the 'indices' function in HQL, you need to have use the list.

Hope this helps
MIKE