Hi,

I have a many-to-many relation between hotels and hotelfacilities.
the problem is that when (for instance) I list all hotels I get duplicates, because a many-to-may relationship without indexcolumn set treats the collection as a bag (so I've read).

For my situation, i don't want this, so I tried adding a @INdexcolmn annotation: @Indexcolumn(name="hotelid"). As I understand it, the doucmentation in my opinion is not very clear about this, this is used to dedup all hotels with the same hotelid and thus return a proper list of hotels without duplicates. (please say if this is wrong)

However when trying to add the @INdexcolumn annoation I get the error:
"
org.hibernate.MappingException: Repeated column in mapping for collection: com.wrappt.aroer.models.Hotel.facilities column: hotelid"

Obviously, because "hotelid" is already defined in JoinColumn, see below.

So in other words: I simply need to get a manytoMany working where both ends of the relation are dedupped. Anyone?

Below are the :
1. mapping documents WITH @IndexColumn in place
2. SQL without @Indexcolumn in place which shows the join which makes for the duplicates.

Thanks,
G.


Mapping documents:

HOTEL
-------------------
@ManyToMany(fetch = FetchType.EAGER)
@JoinTable(
name = "FacilitiesPerHotel",
joinColumns = {@JoinColumn(name = "hotelid")},
inverseJoinColumns = {@JoinColumn(name = "hotelfacilityid")}
)
@IndexColumn(name="hotelid")
@OrderBy("id")
@Cache(usage = org.hibernate.annotations.CacheConcurrencyStrategy.READ_ONLY)
private List<HotelFacility> facilities = new ArrayList<HotelFacility>();


HotelFacility
---------------
@ManyToMany(mappedBy = "facilities")
private Set<Hotel> hotels = new HashSet<Hotel>();


SQL
Hibernate: select this_.id as id70_3_, this_.cityid as cityid70_3_, this_.creation_date as creation2_70_3_, this_.description as descript3_70_3_, this_.latitude as latitude70_3_, this_.longitude as longitude70_3_, this_.modification_date as modifica6_70_3_, this_.name as name70_3_, this_.stars as stars70_3_, this_.status as status70_3_, this_.streetAddress as streetA10_70_3_, city2_.id as id69_0_, city2_.admin1_code as admin2_69_0_, city2_.admin2_code as admin3_69_0_, city2_.alternames as alternames69_0_, city2_.countryid as countryid69_0_, city2_.creation_date as creation5_69_0_, city2_.elevation as elevation69_0_, city2_.existsapriori as existsap7_69_0_, city2_.geonameid as geonameid69_0_, city2_.gtopo30 as gtopo9_69_0_, city2_.latitude as latitude69_0_, city2_.longitude as longitude69_0_, city2_.modification_date as modific12_69_0_, city2_.name as name69_0_, city2_.nrOfHotelsInfluencedLatLong as nrOfHot14_69_0_, city2_.population as population69_0_, city2_.status as status69_0_, city2_.streekid as streekid69_0_, city2_.timezone as timezone69_0_, country3_.id as id68_1_, country3_.alternames as alternames68_1_, country3_.continentid as continen3_68_1_, country3_.country_code as country4_68_1_, country3_.latitude as latitude68_1_, country3_.longitude as longitude68_1_, country3_.name as name68_1_, facilities4_.hotelid as hotelid5_, hotelfacil5_.id as hotelfac2_5_, hotelfacil5_.id as id72_2_, hotelfacil5_.name as name72_2_ from hotels this_ inner join cities city2_ on this_.cityid=city2_.id left outer join countries country3_ on city2_.countryid=country3_.id left outer join FacilitiesPerHotel facilities4_ on this_.id=facilities4_.hotelid left outer join hotelfacilities hotelfacil5_ on facilities4_.hotelfacilityid=hotelfacil5_.id where this_.cityid=? order by hotelfacil5_.id asc

@Index colum indicates a collection index column so primary key is not a good idea. I would you need to add a new column to the relation table and set that as the index column. This index column will hold the order of facilities in a hotel. However, you can use a set instead of list if facilities of a hotel are not ordered, and the same mapping without indexcolumn will work for you.



Farzad-