I'm attempting to create a filter which needs to do a JOIN in order to access the field that I want to filter on. I was able to do this successfully using code like the following.

List<Section> sl = s.createFilter(c2.getSections(), "SELECT DISTINCT this FROM this.sectionTimes st JOIN st.faculty f WHERE f.CNetId = :cNetId").setParameter("cNetId", cNetId).list();

Note, I don't know if the fact that I'm using "this" in the HQL is documented or not. I just guessed on it and it seems to work.

Now, I want put this filter into my mapping file, so, I can make things a little easier from a coding perspective and do something like the following.

s.enableFilter("faculty").setParameter("cNetId", cNetId);
List<Section> sl = c2.getSections();

However, I get the exception which is listed below. One thing that could really be used in the documentation IMHO is examples demonstrating filtering using JOINs.

Hibernate version: 3.0beta1

Mapping documents:

<?xml version="1.0"
encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping
PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="mypackage.Course"
table="sss_offerings"
mutable="false">
<id name="id"
column="sss_offeringsid">
<generator class="native"/>
</id>
<bag name="names"
order-by="name"
table="sss_offerings"
lazy="true">
<key column="parentid"/>
<element column="name"
type="string"/>
</bag>
<bag name="sections"
inverse="true"
lazy="true">
<key column="sss_offeringsid"/>
<one-to-many class="mypackage.Section"/>
<filter name="faculty"
condition="SELECT DISTINCT this FROM this.sectionTimes st JOIN st.faculty f WHERE f.CNetId = :cNetId"/>
</bag>
</class>
<filter-def name="faculty">
<filter-param name="cNetId"
type="string"/>
</filter-def>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

s.enableFilter("faculty").setParameter("cNetId", cNetId);
List<Section> sl = c2.getSections();

Full stack trace of any exception that occurs:

[java] org.hibernate.exception.GenericJDBCException: could not initialize a
collection: [edu.uchicago.at.Course.sections#73491]
[java] at org.hibernate.exception.SQLStateConverter.handledNonSpecificE
xception(SQLStateConverter.java:82)
[java] at org.hibernate.exception.SQLStateConverter.convert(SQLStateCon
verter.java:70)
[java] at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExcep
tionHelper.java:43)
[java] at org.hibernate.loader.Loader.loadCollection(Loader.java:1193)
[java] at org.hibernate.loader.OneToManyLoader.initialize(OneToManyLoad
er.java:103)
[java] at org.hibernate.collection.AbstractCollectionPersister.initiali
ze(AbstractCollectionPersister.java:413)
[java] at org.hibernate.event.DefaultInitializeCollectionEventListener.
onInitializeCollection(DefaultInitializeCollectionEventListener.java:53)
[java] at org.hibernate.impl.SessionImpl.initializeCollection(SessionIm
pl.java:1693)
[java] at org.hibernate.collection.AbstractPersistentCollection.initial
ize(AbstractPersistentCollection.java:172)
[java] at org.hibernate.collection.AbstractPersistentCollection.read(Ab
stractPersistentCollection.java:49)
[java] at org.hibernate.collection.Bag.iterator(Bag.java:231)
[java] at edu.uchicago.at.Test.main(Test.java:131)
[java] Caused by: java.sql.SQLException: [GV]Incorrect syntax near the keyw
ord 'SELECT'.
[java] at com.inet.tds.a.a(Unknown Source)
[java] at com.inet.tds.c.byte(Unknown Source)
[java] at com.inet.tds.c.new(Unknown Source)
[java] at com.inet.tds.c.executeQuery(Unknown Source)
[java] at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatch
er.java:103)
[java] at org.hibernate.loader.Loader.getResultSet(Loader.java:1071)
[java] at org.hibernate.loader.Loader.doQuery(Loader.java:347)
[java] at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollec
tions(Loader.java:193)
[java] at org.hibernate.loader.Loader.loadCollection(Loader.java:1188)
[java] ... 8 more

Name and version of the database you are using:

Microsoft SQL Server 2000

The generated SQL (show_sql=true):

This is the code generated when using <filter>:

select sections0_.sss_offeringsid as sss_offe3___,
sections0_.sss_sectionsid as sss_sect1___,
sections0_.sss_sectionsid as sss_sect1_0_,
sections0_.name as name4_0_,
sections0_.sss_offeringsid as sss_offe3_4_0_,
sections0_.cmn_termsid as cmn_term4_4_0_
from sss_sections sections0_
where SELECT DISTINCT sections0_.this FROM this.sectionTimes st JOIN st.faculty f WHERE f.CNetId = ? and sections0_.sss_offeringsid=?

As you can see, it put everything into the WHERE clause. This is different than how it works if you use createFilter(). IMHO, it should work the same way for both.

The following is the working SQL that createFilter() creates:

select distinct this.sss_sectionsid as sss_sect1_,
this.name as name4_,
this.sss_offeringsid as sss_offe3_4_,
this.cmn_termsid as cmn_term4_4_
from sss_sections this
inner join sss_times sectiontim0_ on this.sss_sectionsid=sectiontim0_.sss_sectionsid
inner join sss_timesfaculty faculty1_ on sectiontim0_.sss_timesid=faculty1_.sss_timesid
inner join cmn_faculties faculty2_ on faculty1_.cmn_personsid=faculty2_.cmn_personsid
inner join uoc_cmnpersonscnetvw faculty2_1_ on faculty2_.cmn_personsid=faculty2_1_.cmn_personsid
where this.sss_offeringsid = ? and ((faculty2_1_.cnet=? ))

Debug level Hibernate log excerpt:

The two uses of the word filter do not imply any semantic similarity between the two completely different features.

Thanks. So, using a <filter>, you can only filter on fields in the immediate table then, right?

You can do a subselect, but dont use "this".

Thanks. That makes sense, I didn't think of that. I think I kind of have it working that way using createFilter() (so, in theory, I should be able to get it working using <filter> now). The only downside appears to be that it executes noticeably slower.

If I use a sub-SELECT like the following,

List<Section> sl = s.createFilter(c2.getSections(), "WHERE this.id IN (SELECT DISTINCT s.id FROM mypackage.Section s JOIN s.sectionTimes st JOIN st.faculty f WHERE f.CNetId = :cNetId)").setParameter("cNetId", cNetId).list();

the following SQL is generated,

select this.sss_sectionsid as sss_sect1_,
this.name as name4_,
this.sss_offeringsid as sss_offe3_4_
from sss_sections this
where this.sss_offeringsid = ? and
((this.sss_sectionsid IN(
select distinct section0_.sss_sectionsid
from sss_sections section0_,
sss_times sectiontim1_,
sss_timesfaculty faculty2_,
cmn_faculties faculty3_
inner join uoc_cmnpersonscnetvw faculty3_1_ on faculty3_.cmn_personsid=faculty3_1_.cmn_personsid
where section0_.sss_sectionsid=sectiontim1_.sss_sectionsid
and sectiontim1_.sss_timesid=faculty2_.sss_timesid
and faculty2_.cmn_personsid=faculty3_.cmn_personsid
and ((faculty3_1_.cnet=? )))))

One thing that looks a little odd is the fact that it's joining the tables using the FROM and WHERE clauses rather than using INNER JOINs like it does if I do it the way that I was before. I wouldn't think that would make a difference performance-wise though. Maybe the query optimizer in the DBMS just isn't doing as good of a job?

List<Section> sl = s.createFilter(c2.getSections(), "SELECT DISTINCT this FROM this.sectionTimes st JOIN st.faculty f WHERE f.CNetId = :cNetId").setParameter("cNetId", cNetId).list();

select distinct this.sss_sectionsid as sss_sect1_,
this.name as name4_,
this.sss_offeringsid as sss_offe3_4_
from sss_sections this
inner join sss_times sectiontim0_ on this.sss_sectionsid=sectiontim0_.sss_sectionsid
inner join sss_timesfaculty faculty1_ on sectiontim0_.sss_timesid=faculty1_.sss_timesid
inner join cmn_faculties faculty2_ on faculty1_.cmn_personsid=faculty2_.cmn_personsid
inner join uoc_cmnpersonscnetvw faculty2_1_ on faculty2_.cmn_personsid=faculty2_1_.cmn_personsid
where this.sss_offeringsid = ? and ((faculty2_1_.cnet=? ))

Eeek, I just realized that <filter> uses SQL not HQL...