Filtering Collection at Select

d96-jme · **Joined:** Mon Jan 02, 2006 9:10 am **Posts:** 10

Hi all!

I am trying to write an evaluation program for "consultants", but I am having problems with accessing collections of a fetched object.

I posted a similar post earlier but no success yet so I try to post again with some more detail. There are two objects involved: consultants and questionnaires

The questionnaires are to related to a period. When using the application it is essential to only access the questionnaires related to a selected period and consultant.

What I want to do:
1. Perform ONE selection from the database fetching all consultants and ONLY the questionnaires related for each consultant to the specified period. If possible I only want the question to return instances of consultant (not questionnaires, which should only be accessible through the consultant).

2. Access the questionnaires for each fetched consultant by calling

Code:

consultant.getQuestionnairesForCurrentPeriod();

As earlier mentioned, this code should only return questionnaires related to a specific period.

Is there a way to do this? or is a work around required (perhaps more than one SQL statement)?. Please supply both SQL and Java code. Im getting so frustrated because I do not know what is possible and what is not possible to do with Hibernate.

I provided the mapping files below and the relations between the tables are as follows:
Consultant 1 <--> * Questionnaire <one-many>
Questionnaire * <--> 1 ConsultantLevelQuestionnaire <many-to-one>
ConsultantLevelQuestionnaire * <--> 1 QuestionnairePeriod <many-to-one>

Thanks in advance!

Best regards,
Joakim

Consultant mapping

Code:

<?xml version="1.0"?> 
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" 
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"> 
<!-- Generated 2005-dec-16 12:21:47 by Hibernate Tools 3.1.0 beta1JBIDERC2 --> 
<hibernate-mapping> 

    <class name="test.evalLight.Consultant" table="Konsult"> 
        <id name="id" type="int" unsaved-value="0"> 
            <column name="KonsultID" /> 
            <generator class="native" /> 
        </id> 


        <set name="questionnaires" inverse="true"> 
            <key> 
                <column name="IfyllandeKonsultID" not-null="true" /> 
            </key> 
            <one-to-many class="test.evalLight.Questionnaire" /> 
            
        </set> 
        <set name="targetQuestionnaires" inverse="true" fetch="join"> 
            <key> 
                <column name="GällerKonsultID" not-null="true" /> 
            </key> 
            <one-to-many class="test.evalLight.Questionnaire" /> 

        </set> 
    </class> 
    
</hibernate-mapping> 

Questionnaire Mapping

Code:

<?xml version="1.0"?> 
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" 
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"> 
<!-- Generated 2005-dec-16 12:21:47 by Hibernate Tools 3.1.0 beta1JBIDERC2 --> 
<hibernate-mapping> 
    <class name="test.evalLight.Questionnaire" table="Enkätifyllning"> 
        <id name="id" type="integer"> 
            <column name="EnkätifyllningID" /> 
            <generator class="native" /> 
        </id> 
        <many-to-one name="targetConsultant" class="test.evalLight.Consultant" fetch="select"> 
            <column name="GällerKonsultID" not-null="true" /> 
        </many-to-one> 
        <many-to-one name="consultant" class="test.evalLight.Consultant" fetch="select"> 
            <column name="IfyllandeKonsultID" not-null="true" /> 
        </many-to-one> 
        <many-to-one name="consultantLevelQuestionnaire" class="test.evalLight.ConsultantLevelQuestionnaire" fetch="select"> 
            <column name="EnkätID" not-null="true" /> 
        </many-to-one> 
        <set name="data" inverse="true"> 
            <key> 
                <column name="EnkätifyllningID" not-null="true" /> 
            </key> 
            <one-to-many class="test.evalLight.QuestionnaireData" /> 
        </set> 
    </class> 
</hibernate-mapping> 

ConsultantLevelQuestionnaire Mapping

Code:

<?xml version="1.0"?> 
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" 
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"> 
<!-- Generated 2005-dec-16 12:21:47 by Hibernate Tools 3.1.0 beta1JBIDERC2 --> 
<hibernate-mapping> 
    <class name="test.evalLight.ConsultantLevelQuestionnaire" table="Enkät"> 
        <id name="id" type="integer"> 
            <column name="EnkätID" /> 
            <generator class="native" /> 
        </id> 
        <many-to-one name="period" class="test.evalLight.QuestionnairePeriod" fetch="select"> 
            <column name="EnkätomgångID" not-null="true" /> 
        </many-to-one> 
        <many-to-one name="consultantLevel" class="test.evalLight.ConsultantLevel" fetch="select"> 
            <column name="KonsultnivåID" not-null="true" /> 
        </many-to-one> 
        <property name="name" type="string"> 
            <column name="EnkätNamn" length="50" not-null="true" /> 
        </property> 
        <property name="created" type="timestamp"> 
            <column name="Skapad" length="16" not-null="true" /> 
        </property> 
        <set name="questionnaires" inverse="true"> 
            <key> 
                <column name="EnkätID" not-null="true" /> 
            </key> 
            <one-to-many class="test.evalLight.Questionnaire" /> 
        </set> 

    </class> 
</hibernate-mapping> 

QuestionnairePeriod Mapping

Code:

<?xml version="1.0"?> 
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" 
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"> 
<!-- Generated 2005-dec-16 12:21:47 by Hibernate Tools 3.1.0 beta1JBIDERC2 --> 
<hibernate-mapping> 
    <class name="test.evalLight.QuestionnairePeriod" table="Enkätomgång"> 
        <id name="id" type="integer"> 
            <column name="EnkätomgångID" /> 
            <generator class="native" /> 
        </id> 
        <property name="name" type="string"> 
            <column name="EnkätomgångNamn" length="50" not-null="true" /> 
        </property> 
        <set name="consultantLevelQuestionnaires" inverse="true" order-by="KonsultnivåID asc"> 
            <key> 
                <column name="EnkätomgångID" not-null="true" /> 
            </key> 
            <one-to-many class="test.evalLight.ConsultantLevelQuestionnaire" /> 
        </set> 
    </class> 
</hibernate-mapping> 

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Probably the best option for this (there are a few solutions) would be to use Criteria and returnMaps. Have a look at section "Associations" in chapter "Criteria Queries" of the ref docs.

LaLiLuna · **Posted:** Wed Jan 18, 2006 5:43 pm

Hello,

what is the problem with the query from rajasaur

Quote:

"FROM Consultant c inner join fetch c.questionnaires q where q.consultantLevelQuestionnaire.period=:period")
.setEntity("period", period)
.list();

Apart from the period which you should change to

Code:

q.consultantLevelQuestionnaire.period > :start and
q.consultantLevelQuestionnaire.period < :end

By the way. I would not use Unicode in column names: EnkätomgångNamn
There is always a fair change that by encoding issues you get a problem.

Regards Sebastian

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

With that solution you would get every consultant that has any questionnaires in the period. Each consultant's questionnaire set would hold all of his questionnaires, not just the ones in the relevant period. You'd have to filter the returned list. With returnMaps, you can jump directly to the exact questionnaires that matched the query, thus skipping the filtering step.

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

However, if you changed the query to prepend "select c, q" you would get an array of Object[2]s, with all the appropriate questionnaires matched with the owning consultants. The Object arrays are slightly more cumbersome to navigate, but it would work just as well as the returnMaps option.

Probably the "simplest" solution would be to write a query that returns only the questionnaires. That would be easy, and because questionnaires have a reference to their consultant, the query would be returning all necessary data. You'd want to include the "join fetch" syntax in your HQL, to ensure that the consultants are loaded in the same select statement as the questionnaires.

LaLiLuna · **Posted:** Wed Jan 18, 2006 6:10 pm

No I do not think so.
At least (another example:)
from Department d left join fetch d.teams q where q.name = :name
does only fetch the relevant teams.

Regards Sebastian

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

You mean, with that query, a Department with two teams A and B will seem to only have a single team, A, if "A" is provided as a parameter to the query? I can categorically say that this is not correct. In this case, qry.uniqueResult().getTeams().contains(B) will return true. That query will definitely not allow the developer to get only those teams called A. A query closer to what's needed would be

Code:

select q from Department d left join fetch d.teams q where q.name = :name

which would select teams of the correct name that are in any Department. However, seeing as d96-jme wants (to use your example with his question) the Departments and Teams, a better query would be

Code:

select d, q from Department d left join fetch d.teams q where q.name = :name

I would still use the returnMaps solution, myself.

LaLiLuna · **Posted:** Thu Jan 19, 2006 4:44 am

tenwit, I tested it. If I am not totally lost, it works.

Regards Sebastian

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Odd. I've just double-checked it, and I've verified that with the sort of query mentioned above, I am finding all members of every set where any member of the set matches the query. The documentation certainly explicitly says that what I'm finding is correct:

For criteria (quote from the ref docs):
Note that the kittens collections held by the Cat instances returned by the previous two queries are not pre-filtered by the criteria! If you wish to retrieve just the kittens that match the criteria, you must use returnMaps().

There doesn't seem to be an equivalent statement for HQL queries, but I have tested it and I'm confident that I'm right. I suppose that we must be talking at cross purposes here, and not testing exactly the same thing as each other. Can you post the SQL generated by your test? Including the SQL generated when you iterate over the collection: in the Department/Teams example, I'd like to see the SQL generated from this chunk of code:

Code:

Query qry = session.createQuery("from Department d left join fetch d.teams q where q.name = :name");
qry.setString("name", name);
List<Department> l = qry.list();
for (Department d : l)
{
  for (Team t : d.getTeams())
  {
    System.out.println("Found team " + t.getName());
  }
}

Though if your test shows that teams with getName() != name, then obviously the SQL won't be needed.

LaLiLuna · **Posted:** Sat Jan 28, 2006 2:27 pm

Hello,
I give you my mapping, test data and the testquery I used. The table can be generated by Hibernate.
I used b% and c as parameter to the query.

Regards Sebastian

this is the generated code

Code:

select department0_.id as id19_0_, teams1_.id as id21_1_, department0_.name as name19_0_, teams1_.name as name21_1_, teams1_.department_id as department3_21_1_, teams1_.department_id as department3_0__, teams1_.id as id0__ from examples.tdepartment department0_ left outer join examples.tteam teams1_ on department0_.id=teams1_.department_id where teams1_.name like ?

Code:

Set sets = new HashSet(session
            .createQuery(
                  "from Department d left join fetch  d.teams q where q.name like :name")
            .setString("name", "c").list());
      for (Iterator iter = sets.iterator(); iter.hasNext();) {
         Department element = (Department) iter.next();
         log.debug(element);
         for (Iterator iterator = element.getTeams().iterator(); iterator
               .hasNext();) {
            Team team = (Team) iterator.next();
            log.debug(team);
         }
      }

get the tables created by Hibernate and insert

Code:

delete from tdepartment where id = 1 or id = 2;
delete from tteam where id <= 4;
insert into tdepartment (id,name) values (1,'a department');
insert into tdepartment (id,name) values (2,'b department');

insert into tteam (id,name,department_id) values (1,'a',1);
insert into tteam (id,name,department_id) values (2,'b',1);
insert into tteam (id,name,department_id) values (3,'b2',2);
insert into tteam (id,name,department_id) values (4,'c',2);

mapping department

Code:

<hibernate-mapping  package="de.laliluna.example4">
  <class name="Department" table="tdepartment" >
    <id name="id" >
      <generator class="sequence">
        <param name="sequence" >department_id_seq
        </param>
      </generator>
    </id>
    <property name="name" type="string"></property>
    <set name="teams" table="tteam" inverse="false" cascade="persist,lock,replicate,save-update,delete,delete-orphan,refresh,evict,replicate">
      <key column="department_id"></key>
      <one-to-many class="Team"/>
      
    </set>
  </class>

</hibernate-mapping>

mapping team

Code:

<hibernate-mapping package="de.laliluna.example4">
  <class name="Team" table="tteam" >
    <id name="id" >
      <generator class="sequence">
        <param name="sequence" >team_id_seq</param>
      </generator>
    </id>
    <property name="name" type="string"></property>
    <many-to-one name="department" class="Department">
    <column name="department_id" not-null="true"></column>
    </many-to-one>
    
  </class>

</hibernate-mapping>

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

I ran that code verbatim. My results were:

For name = "c":

Department b department
Team b2 (in dept. Department b department)
Team c (in dept. Department b department)

For name = "b%:

Department b department
Team b2 (in dept. Department b department)
Team c (in dept. Department b department)
Department a department
Team a (in dept. Department a department)
Team b (in dept. Department a department)

This corroborates my assertions: that the query finds the correct Departments and all Teams in them. It does not find only the teams that match the query.

LaLiLuna · **Posted:** Mon Jan 30, 2006 6:18 am

Hello Tenwit,
I really want to figure this out. This is quite bizarre.
Which Hibernate version do you use and which database?
I use
Hibernate 3.1 with PostgreSQL 8.1
EH Cache is configured

The select of course returns the collection without the "non fitting" teams.
So when Hibernate does create the objects directly from the select then my result would occure.
When Hibernate does look for all fitting departments and then select the team collection information, than of course your result should happen.

b% has the following result:

Code:

Hibernate: select department0_.id as id19_0_, teams1_.id as id21_1_, department0_.name as name19_0_, teams1_.name as name21_1_, teams1_.department_id as department3_21_1_, teams1_.department_id as department3_0__, teams1_.id as id0__ from examples.tdepartment department0_ left outer join examples.tteam teams1_ on department0_.id=teams1_.department_id where teams1_.name like ?
59:15,265 DEBUG TestQuery:52 - Department: 1 Name: a department
59:15,265 DEBUG TestQuery:56 - Team: 2 Name: b
59:15,265 DEBUG TestQuery:52 - Department: 2 Name: b department
59:15,265 DEBUG TestQuery:56 - Team: 3 Name: b2

Damn, what is really worse, I run a second query to achieve your behaviour directly after the first. This query does return my results but uses the collections from the cache, which is of course wrong. After adding a session.refresh, I got your results.
This would be a dangerous problem you must be aware when working in the same session.

Regards Sebastian

Code:

 sets = new HashSet(session
            .createQuery(
                  "from Department d  where d.teams.name like :name")
            .setString("name", "b%").list());
      for (Iterator iter = sets.iterator(); iter.hasNext();) {
         Department element = (Department) iter.next();
         session.refresh(element);
         log.debug(element);
         for (Iterator iterator = element.getTeams().iterator(); iterator
               .hasNext();) {
            Team team = (Team) iterator.next();
            log.debug(team);
         }
      }

Just to verify this with yours:
Hibernate configuration
<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">


<hibernate-configuration>

<session-factory>
<property name="myeclipse.connection.profile">library-web</property>
<property name="connection.url">jdbc:postgresql://localhost:5432/libraryweb</property>
<property name="connection.username">postgres</property>
<property name="connection.password">p</property>
<property name="connection.driver_class">org.postgresql.Driver</property>
<property name="dialect">org.hibernate.dialect.PostgreSQLDialect</property>

<property name="cache.provider_class">org.hibernate.cache.EhCacheProvider</property>

<property name="current_session_context_class">thread</property>
<property name="hibernate.transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</property>

<property name="hibernate.show_sql">true</property>
<property name="hibernate.default_schema">examples</property>

<mapping resource="de/laliluna/example4/Department.hbm.xml" />
<mapping resource="de/laliluna/example4/Member.hbm.xml" />
<mapping resource="de/laliluna/example4/Team.hbm.xml" />

</session-factory>

</hibernate-configuration>

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

I'm using 3.0.5 with SQLServer2000.

I'm still convinced that my results are more correct. Not only do they fit in with the docs, they also fit in with what's "logical", imo. The query is for all departments that have matching teams. It isn't for teams, at all. The only reason teams are coming out of the query is because departmants have sets of teams in them. There is no filtering on those sets, so they should contain all of the departments' teams. If it worked for me the way it's working for you, I'd raise a JIRA issue.