HI there,

I am trying to use Hibernate Filters. The filter tag is the place where it is possible to add a condition i.e.
<filter name="projectFilter" condition=":projectFilterParam = id" />

I am trying to apply a join in the above filter but hibernate gives an error.

Another thing, my hibernate mapping file has a column which is a foreign key, i am not able to use that foreign key in my condition. Here is what i am writing for my condition:

<class name="Domain" table="doamin">

<id name="id" column="id" type="java.lang.Integer">
<generator class="hilo" />
</id>

<set name="associateStakeholder" table="domain_stakeholder"
lazy="false">
<key column="domain_id" />
<many-to-many column="associate_id"
class="lotusssnd.projections.domain.Associate" />
</set>

</class>

i am trying to use the associate_id column in my condition, but an error occurs, is there any way i can use this.

Thanks.

You'll need to include the error. We can't diagnose a problem unless we know what the problem is.



Assuming that you are writing your condition as an HQL query or a Criteria, then you need to use the property, not the column name. The property is "associateStakeHolder".

I am actually enabling the filter and setting the value of filter parameters for project id and associate id. This is my complete xml mapping definition for Domain entity (Domain class), the domain table's column: domain_stakeholder holds an id of an employee (foreign key). This column basically has a many-to-many relationship with the
associate_id (employee_id) of the domain_stakeholder table (i have used <set> here)---

<class name="Domain" table="domain">
<id name="id" column="id" type="java.lang.Integer">
<generator class="hilo" />
</id>
<version name="version" column="version" />

<property name="domainName" column="domain_name"
type="java.lang.String" />
<property name="billedResource" column="billed_resource"
type="java.lang.Double" />

<set name="associateStakeholder" table="domain_stakeholder"
lazy="false">
<key column="domain_id" />
<many-to-many column="associate_id"
class="lotusssnd.projections.domain.Associate" />
</set>

<filter name="projectFilter" condition=":projectFilterParam = id" />
<filter name="associateFilter" condition=":associateFilterParam = domain_stakeholder.associate_id"/>

</class>

<filter-def name="projectFilter">
<filter-param name="projectFilterParam" type="java.lang.Integer" />
</filter-def>
<filter-def name="associateFilter">
<filter-param name="associateFilterParam" type="java.lang.Integer" />
</filter-def>

i am enabling the filters and setting the values for projectfilterparam and associatefilterparam, but i am getting this error:

Hibernate operation: could not execute query; bad SQL grammar [select domain0_.id as id, domain0_.version as version6_, domain0_.domain_name as domain3_6_, domain0_.billed_resource as billed4_6_ from prod.domain domain0_ where :associateFilter.associateFilterParam = domain_stakeholder.associate_id and :projectFilter.projectFilterParam = domain0_.id order by domain0_.domain_name]; nested exception is org.postgresql.util.PSQLException: ERROR: missing FROM-clause entry for table "domain_stakeholder"

is is possible to write the filter in such a manner (joins).
<filter name="projectFilter" condition=":projectFilterParam = id and :associateFilterParam = domain_stakeholder.associate_id/>

Please help

There's a couple of problems in there. With filters, you can't refer to tables except via their alias, and you can't do anything that doesn't work in a where clause. So you can't fix your problem: you can't refer to the alias that hibernate generates for domain_stakeholder (because you don't know what it is), and you can't join to the table yourself (because you can't join inside a where clause). You could use a subselect, but I'm not sure that that would actually solve your issue.

I think that you can use <set>'s where="" attribute for that sort of thing, as hibernate will handle aliases etc. for you.

Assuming that you can rewrite your filter without table names, etc., are you certain that you should be setting these up as class filters? Becaue they look like they should probably be collection filters. That is, the filter belongs inside the <set>. Not only does that reduce the likelihood of putting the filter in the wrong class (and I think your associateFilter belongs in the Associate class, if it's to be a class filter), it also ensures that it is applied only when you want it to be. Class filters work on on Criteria and HQL as well as associations (though I believe that they don't apply to Session.get/load).

I'm leaving now, if you have further problems I'll be back in the morning. Good luck.