where clauses in associative statements

ccronemberger · **Joined:** Mon Oct 25, 2004 10:18 am **Posts:** 1

I think that the generated SQL statement should have the condition "where status != 'Deleted'" at the end because this condition is defined in the definition of the User class. I thought that I could use a view to solve this problem, but MySQL does not support it yet.
Another option would be to use a where attribute in the set element, but it does not work because the field that is used in the condition is from the USERS table and not from the USERGROUP table.
I have debugged the code a little bit, and I have found that the problem is that the class OuterJoinLoader.OuterJoinableAssociation does not have any attribute for a where clause.

Is this something that is going to be on Hibernate 3?

Hibernate version: 2.1.6, 9.8.2004

Mapping documents:

Code:

<class name="org.Group" table="GROUPS" where="status != 'Deleted'">
        <id name="groupId" type="int" unsaved-value="0" >
            <generator class="identity"/>
        </id>
        <set name="members" table="USERGROUP">
            <key column="groupid"/>
            <many-to-many column="userid" class="org.User"/>
        </set>
</class>

<class name="org.User" table="USERS" where="status != 'Deleted'">
        <id name="userId" type="int" unsaved-value="0" >
            <generator class="identity"/>            
        </id>
</class>

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

Code:

Group g = (Group)session.get(Group.class,new Integer(107));
System.out.println("members: "+g.getMembers());

Full stack trace of any exception that occurs: no exceptions

Name and version of the database you are using: MySQL 4.0.20-standard

The generated SQL (show_sql=true):

Code:

select members0_.groupid as groupid__, members0_.userid as userid__, user1_.userId as userId0_ from USERGROUP members0_ inner join USERS user1_ on members0_.userid=user1_.userId where members0_.groupid=?

Debug level Hibernate log excerpt: not necessary

The tables are:

Code:

CREATE TABLE USERS (
   userid mediumint NOT NULL auto_increment,
        status ENUM('Enable','Disable','Deleted') NULL DEFAULT 'Enable',
   PRIMARY KEY (userid),
   INDEX USERS_USERID_INDEX(userid)
) TYPE=InnoDB;

CREATE TABLE GROUPS (
  groupid MEDIUMINT(9) NOT NULL AUTO_INCREMENT,
  status ENUM('Enable','Disable','Deleted') NULL DEFAULT 'Enable',
  PRIMARY KEY(groupid),
  INDEX GROUPS_GROUPID_INDEX(groupid)
) TYPE=InnoDB;

CREATE TABLE USERGROUP (
   usergroupid MEDIUMINT(9)  NOT NULL AUTO_INCREMENT,
   userid MEDIUMINT  NOT NULL,
   groupid MEDIUMINT  NOT NULL,
   PRIMARY KEY(usergroupid),
   INDEX USERGROUP_USERID_INDEX(userid),
   INDEX USERGROUP_GROUPID_INDEX(groupid)
) TYPE=InnoDB;

stolley · **Posted:** Mon Oct 25, 2004 7:32 pm

Check out the "where" attribute of the "class" mapping element, does that do what you want?

http://www.hibernate.org/hib_docs/refer ... pping.html
Section 5.1.3.

anthony · **Posted:** Tue Oct 26, 2004 3:20 am

you also have to declare where attribute on many-to-many element

curio · **Joined:** Tue Oct 05, 2004 9:45 am **Posts:** 263

@anthony
will the 'where'-attribute for many-to-many/many-to-one find his way to hibernate2? or is this a 'hibernate3-only'-feature?

thx
curio

gavin · **Posted:** Tue Oct 26, 2004 3:56 am

Actually, there is currently no way to filter a many-to-many by columns of the associated table (only by the association table).

We certainly need to add a where attribute (and HB3 filter support) to the <many-to-many> element.

Note that class-level filters do NOT get applied to collection loading, you should not expect them to.