Register function error - complex order by clause

poroto20 · **Joined:** Tue Feb 03, 2009 2:49 pm **Posts:** 4

I need a complex order by, because the field to order by is not in the same table of the main entity. So I tried to register a function that returns that field.

These are the classes involved:

Code:

.....
@Table(name = "product")
public class ProductImpl extends RelationableImpl implements Product {

   private Map<String, Attribute> attributes = new HashMap<String, Attribute>();

   @OneToMany(targetEntity = AttributeImpl.class, fetch = FetchType.LAZY, cascade = CascadeType.ALL)
   @MapKey(name = "name")
   public Map<String, Attribute> getAttributes() {
      return attributes;
   }

   public void setAttributes(Map<String, Attribute> attributes) {
      this.attributes = attributes;
   }
....
}
......
@Table(name = "attribute")
public class AttributeImpl extends BaseObjectImpl implements Attribute {

   private String name;
   private String value;
............... (get and set)
 }

I've registered this function in my own MySql dialect:

Code:

......
registerFunction(
            "get_dynamic_attribute",
            new SQLFunctionTemplate(
                  Hibernate.STRING,
                  "SELECT att.VALUE FROM attribute AS att WHERE att.id IN (SELECT attributes_id FROM product_attribute WHERE product_id=?1) and att.name=?2"));

And I called the function above with this hql:

Code:

"select distinct............. order by get_dynamic_attribute(product.id,'vendor_part') asc"

I want s list of product order by a dynamic attribute called vendor_part, but when I run the code above I get this exception:

Code:

.......
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
.......
Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT att.VALUE FROM attribute AS att WHERE att.id IN (SELECT attributes_id FRO' at line 1
.......

Anybody can help me and tell me what I'm doing wrong please??

Or, what can I do to get a list of products order in this way??