Hibernate version:
2.1.6

Hi,

I have a class named "Entity", mapped to table "ENTITIES" (see below). I have defined a map collection into "Entity", to obtain properties of an entity, as shown in the following markup

/**
* @hibernate.map name="textAttributes" table="ENTITY_ATTRIBUTES" lazy="true" cascade="all-delete-orphan"
* @hibernate.collection-key column="ENTITY"
* @hibernate.index-many-to-many column="ATTRIBKEY" class="nf.bsf.beans.common.Name"
* @hibernate.collection-element column="ATTRIBVALUE_TEXT" type="java.lang.String"
* @return Map
*/

So, cleanly I have only one entity, and I can map arbitrary properties to this entity.

My problem comes when I want (with HQL) to obtain the entities ordered by the value of a property of the map (i.e. order the entities by a Social Security number). The problem is to order by the values of a "specific" property (i.e. a key in the map)

My first trial is to restrict the rows fetched(to only the properties I want, speaking, Social Security number) by the inner join and then order by these join values...

select entity from Entity entity inner join entity.textAttributes[:key] as entity_textattrs ... group by entity_textattrs

But hibernate complains about that (the error is "unexpected token :key").

Do you have any hints or ideas??.

Thank you very much in advance...

Carlos

CREATE TABLE ENTITIES
(
ID_ENTITY NUMBER(20) NOT NULL,
TYPE NUMBER(20) NOT NULL,
STATUS NUMBER(20) NOT NULL,
CONSTRAINT PK_ENTITIES PRIMARY KEY (ID_ENTITY),
CONSTRAINT FK_TYPE FOREIGN KEY (TYPE) REFERENCES NAMES(ID_NAME),
CONSTRAINT FK_STATUS FOREIGN KEY (STATUS) REFERENCES NAMES(ID_NAME)
);

and

CREATE TABLE ENTITY_ATTRIBUTES
(
ENTITY NUMBER(20) NOT NULL,
ATTRIBKEY NUMBER(20) NOT NULL,
ATTRIBVALUE_TIMESTAMP TIMESTAMP,
ATTRIBVALUE_NUMBER NUMBER,
ATTRIBVALUE_TEXT VARCHAR2(2048),
CONSTRAINT PK_ENTITY_ATTRIBUTES PRIMARY KEY(ENTITY, ATTRIBKEY),
CONSTRAINT FK_ENTITY FOREIGN KEY(ENTITY) REFERENCES ENTITIES(ID_ENTITY),
CONSTRAINT FK_ATTRIBKEY FOREIGN KEY(ATTRIBKEY) REFERENCES NAMES(ID_NAME)
);