Trouble accessing references - HQL question

bbonner · **Joined:** Fri Sep 12, 2003 12:40 pm **Posts:** 65

I'm trying to retrieve the children of a set of children based on the following SQL and mapping files. The results follow.

Example:

Code:

Query query = session.createQuery("SELECT pr from ProductReview as pr where pr.id = ? ");
           query.setString(0, "402882c5f7d7367100f7d736b3900001");
List summaryList = query.list();
System.out.println(" # of product reviews:"+summaryList.size());
for (Iterator iter = summaryList.iterator(); iter.hasNext();) {
   ProductReview element = (ProductReview) iter.next();
   Set attributes = element.getProduct().getSubCategory().getCategory().getAttributes();
   System.out.println(" # of attributes: "+attributes.size());
   for (Iterator iter2 = attributes.iterator(); iter2.hasNext();) {
     Attribute element2 = (Attribute) iter2.next();
     System.out.println(" # of selected attribute values: "+element2.getSelectedAttributeValues().size()+ " for attribute: "+element2);
   }
   Set selectedAttributeValues = element.getSelectedAttributeValues();
   System.out.println(" # of selected attribute values for product_review: "+selectedAttributeValues.size());
   for (Iterator iter2 = selectedAttributeValues.iterator(); iter2.hasNext();) {
      SelectedAttributeValue value = (SelectedAttributeValue) iter2.next();
      System.out.println(" selected attribute value:"+value+" attribute:"+value.getAttribute());
   }
 }

Results:

Code:

 # of product reviews:1
 # of attributes: 2
 # of selected attribute values: 0 for attribute: Attribute@15f1f9c[id=402882c5f7d7367100f7d736eea10003]
 # of selected attribute values: 0 for attribute: Attribute@e90943[id=402882c5f7d7367100f7d736b3900001]
 # of selected attribute values for product_review: 3
 selected attribute value:SelectedAttributeValue@f727a1d3 attribute:Attribute@15f1f9c[id=402882c5f7d7367100f7d736eea10003]
 selected attribute value:SelectedAttributeValue@add59d81 attribute:Attribute@e90943[id=402882c5f7d7367100f7d736b3900001]
 selected attribute value:SelectedAttributeValue@d7370a03 attribute:Attribute@15f1f9c[id=402882c5f7d7367100f7d736eea10003]

Notice that when I go for the 1 to many from ProductReview to SelectedAttributeValues, I get them all AND the associated references back to the Attribute. However, when I try to navigate the many-to-ones back to the Attributes, I can't get the SelectedAttributeValues for each Attribute.

I'm looking for the equivalent of (I'm only pulling the IDs for brevity):

Code:

select * from (select product_review.product_review_id, product.product_id, office.office_id, attribute.attribute_id
from product_review, product, subcategory, category, office, attribute 
where product_review.product_id = product.product_id
and product_review.office_id = office.office_id
and product.subcategory_id = subcategory.subcategory_id
and subcategory.category_id = category.category_id
and category.category_id = attribute.category_id
and product_review.product_review_id = '402882c5f7d7367100f7d736b3900001') as a
left join selected_attribute_value
on a.attribute_id = selected_attribute_value.attribute_id
and a.product_review_id = selected_attribute_value.product_review_id

I've tried:

Code:

SELECT pr from ProductReview as rr where pr.id = ? left join fetch SelectedAttributeValue sav where pr.id = sav.productReview.id and sav.attribute.id = pr.product.subcategory.category.attributes.id 

which yields:

Code:

net.sf.hibernate.QueryException: expecting 'elements' or 'indices' after: productRev0_.product.subcategory.category.attributes.id [SELECT pr from ProductReview as rr where pr.id = ? left join fetch SelectedAttributeValue sav where pr.id = sav.productReview.id and sav.attribute.id = pr.product.subcategory.category.attributes.id ]
   at net.sf.hibernate.hql.PathExpressionParser.doCollectionProperty(PathExpressionParser.java:496)
   at net.sf.hibernate.hql.PathExpressionParser.token(PathExpressionParser.java:189)
   at net.sf.hibernate.hql.WhereParser.doPathExpression(WhereParser.java:364)
   at net.sf.hibernate.hql.WhereParser.doToken(WhereParser.java:393)
   at net.sf.hibernate.hql.WhereParser.token(WhereParser.java:279)
   at net.sf.hibernate.hql.ClauseParser.token(ClauseParser.java:87)
   at net.sf.hibernate.hql.PreprocessingParser.end(PreprocessingParser.java:139)
   at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:30)
   at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:146)
   at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:133)
   at net.sf.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:352)
   at net.sf.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:330)
   at net.sf.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:1368)
   at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1332)
   at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:76)
   at test.TestQuery.main(TestQuery.java:71)
Exception in thread "main" 

I'm stumped on this one. Can someone lend me a hand??

Here's my mapping code:

Code:

<hibernate-mapping>
    <class name="ProductReview" table="review">
        <id name="id" type="string" unsaved-value="0">
            <column name="review_id" length="32" not-null="true"/>
            <generator class="uuid.hex"/>
        </id>
   <many-to-one name="product" class="Product" column="product_id" not-null="true" />
   <many-to-one name="SalesOffice" class="SalesOffice" column="office_id" not-null="true" constrained="true"/>
   <set name="oems" table="review_oem_link"
      cascade="all" lazy="true" >
      <key column="review_id"/>
      <many-to-many class="Oem" column="oem_id"/>
   </set>
   <set name="selectedAttributeValues" table="selected_attribute_value"
      cascade="all" inverse="true" lazy="true">
      <key column="review_id"/>
      <one-to-many class="SelectedAttributeValue"/>
   </set>
    </class>
</hibernate-mapping>

<hibernate-mapping>
    <class name="SalesOffice" table="office">
        <id name="id" type="string" unsaved-value="0">
            <column name="office_id" length="32" not-null="true"/>
            <generator class="uuid.hex"/>
        </id>
        <property name="name" column="office_name" type="string" length="40" unique="true" not-null="true"/>
   <set name="productReviews" table="review"
      cascade="all" inverse="true" lazy="true">
      <key column="office_id"/>
      <one-to-many class="ProductReview"/>
   </set>
   <set name="users" table="user_office_link"
      cascade="all" lazy="true" >
      <key column="office_id"/>
      <many-to-many class="User" column="user_id"/>
   </set>
    </class>
</hibernate-mapping>

<hibernate-mapping>
    <class name="Subcategory" table="subcategory">
      <id name="id" type="string" unsaved-value="0">
            <column name="subcategory_id" length="32" not-null="true"/>
            <generator class="uuid.hex"/>
        </id>
        <property name="description">
            <column name="desc" sql-type="varchar(255)" />
        </property>
   <many-to-one name="category" class="Category" column="category_id"/>
   <set name="products" table="product"
      cascade="all" inverse="true" lazy="true">
      <key column="subcategory_id"/>
      <one-to-many class="Product"/>
   </set>
    </class>
</hibernate-mapping>

<hibernate-mapping>
    <class name="Category" table="category">
        <id name="id" type="string" unsaved-value="0">
            <column name="category_id" length="32" not-null="true"/>
            <generator class="uuid.hex"/>
        </id>
        <property name="description" column="desc" type="string" length="255"/>
   <many-to-one name="department" class="Department" column="dept_id"/>
   <set name="subcategories" table="subcategory" 
      cascade="all" inverse="true" lazy="true" >
      <key column="category_id"/>
      <one-to-many class="Subcategory"/>
   </set>
   <set name="attributes" table="attribute"
      cascade="all" inverse="true" lazy="true">
      <key column="category_id"/>
      <one-to-many class="Attribute"/>
   </set>
    </class>
</hibernate-mapping>

<hibernate-mapping>
    <class name="Department" table="department">
      <id name="id" type="string" unsaved-value="0">
            <column name="dept_id" length="32" not-null="true"/>
            <generator class="uuid.hex"/>
        </id>
        <property name="description">
            <column name="dept_desc" sql-type="varchar(30)" not-null="true"/>
        </property>
   <set name="categories" cascade="all" 
      lazy="true" table="category" inverse="true">
      <key column="dept_id"/>
      <one-to-many class="Category"/>
   </set>
    </class>
</hibernate-mapping>

<hibernate-mapping>
    <class name="Attribute" table="attribute">
        <id name="id" type="string" unsaved-value="0">
            <column name="attribute_id" length="32" not-null="true"/>
            <generator class="uuid.hex"/>
        </id>
        <property name="attributeName" column="attribute_name" type="string" length="50"/>
        <property name="attributeType" column="attribute_type" type="AttributeType"/>
      <set name="attributeChoices" table="attribute_choice" 
         cascade="all" inverse="true" lazy="true">
         <key column="attribute_id"/>
         <one-to-many class="AttributeChoice"/>
      </set>
      <many-to-one name="category" class="Category" column="category_id" />
    </class>
</hibernate-mapping>

<hibernate-mapping>
    <class name="AttributeChoice" table="attribute_choice">
        <id name="id" type="string" unsaved-value="0">
            <column name="attribute_choice_id" length="32" not-null="true"/>
            <generator class="uuid.hex"/>
        </id>
        <property name="value" column="attribute_choice_value" type="string" length="30"/>
      <many-to-one name="attribute" class="Attribute" column="attribute_id"/>
    </class>
</hibernate-mapping>

<hibernate-mapping>
    <class name="SelectedAttributeValue" table="selected_attribute_value">
        <id name="id" type="string" unsaved-value="0">
            <column name="sav_id" length="32" not-null="true"/>
            <generator class="uuid.hex"/>
        </id>
        <property name="selectedValue" column="selected_value" type="string" length="30"/>
        <many-to-one name="attribute" class="Attribute" column="attribute_id" />
        <many-to-one name="ProductReview" class="ProductReview" column="review_id" />
    </class>
</hibernate-mapping>

<hibernate-mapping>
    <class name="Oem" table="oem">
        <id name="id" type="string" unsaved-value="0">
            <column name="oem_id" length="32" not-null="true"/>
            <generator class="uuid.hex"/>
        </id>
        <property name="name" column="oem_name" type="string" length="40" unique="true" not-null="true"/>
        <set name="ProductReviews" table="review_oem_link"
         cascade="all" lazy="true">
         <key column="oem_id"/>
         <many-to-many class="ProductReview" column="review_id"/>
   </set>
    </class>
</hibernate-mapping>

gavin · **Posted:** Tue Oct 14, 2003 7:14 pm

this is actually a faq.

you must assign an alias to the elements of pr.product.subcategory.category.attributes in the from clause.

bbonner · **Joined:** Fri Sep 12, 2003 12:40 pm **Posts:** 65

I'll go check the faq to see what you mean.

bbonner · **Joined:** Fri Sep 12, 2003 12:40 pm **Posts:** 65

Gavin, I'm afraid I scoured the faq and came up empty.

I found:

Quote:

How do a write a query that returns objects based upon a WHERE clause condition applied to their collection elements?

There are two possibilities:

select distinct parent
from Parent parent,
join parent.children child
where child.name = :name

from Parent parent where :childId in elements(parent.children)

The first query uses a table join; the second uses a subquery. The first query allows constraints to be applied to element properties; the second does not.

You may not need the distinct keyword in the first query.

But I don't think it exactly applies. Did I misunderstand you to say that it's in the FAQ? (or it *is* a FAQ).

Anyway, I used this (which is what I think you meant) and it worked:

Code:

String sql =
      "SELECT pr from ProductReview as pr, "
        + " elements(pr.product.subcategory.category.attributes) as sav " 
        +   " where pr.id = ? ";

Query query = session.createQuery(sql);

Is there an explanation of the behaviour of how the HQL parser works? Can someone explain to me what it did? On hibernate.org I've seen elements used like:

Are the two syntaxes below the same?
pr.product.subcategory.category.attributes.elements and elements(pr.product.subcategory.category.attributes)

gavin · **Posted:** Wed Oct 15, 2003 1:15 pm

in Hibernate2 the recommended way to write this query is:

select pr
from ProductReview as pr
join pr.product.subcategory.category.attributes as sav
where sav.id = ?

Use of "elements" in the from clause is deprecated. (the "attribute form" and "functional from" are equivalent, btw ... functional form is now preferred)

bbonner · **Joined:** Fri Sep 12, 2003 12:40 pm **Posts:** 65

Thanks for the help.

I don't think the queries would be the same then. I need them by pr.id = ? Was this a typo?

Did the use of elements get deprecated since you last wrote this:

Quote:

you must assign an alias to the elements of pr.product.subcategory.category.attributes in the from clause.

I prefer the functional form of the elements syntax, I just didn't see any verbage about this.