where attribute ignored when using many to many

anthony · **Posted:** Wed Mar 31, 2004 11:02 am

hi,
i have this:

Code:

<hibernate-mapping>
    <class        name="com.auchan.refUniqueServices.infosProduits.bo.ElementNomenclature"
        table="P_N05_L19"
        dynamic-update="false"
        dynamic-insert="false"
        mutable="false"
        [b]where="NSU_DEN='450008'"[/b]
        lazy="false"
        
    >
....
</hibernate-mapping>

when i execute this query

Code:

query = "from com.auchan.refUniqueServices.infosProduits.bo.ElementNomenclature e where e.id =730030008";

where NSU_DEN='450008' is generated

but i have a many to many declared on another class

Code:

<hibernate-mapping>
    <class
        name="com.auchan.refUniqueServices.infosProduits.bo.Article"
        table="P_ART_ARTICLE"
        dynamic-update="false"
        dynamic-insert="false"
        mutable="false"
    >
...
        <set
            name="familles"
            table="P_L01_ART_NCL"
            lazy="false"
            inverse="false"
            cascade="none"
            sort="unsorted"
            order-by="DRA_ART_L01 DESC"
            where="NSU_VRN='30008' and DAT_FIN_L01 is null"
        >

              <key
                  column="NSU_ART"
              />

              <many-to-many
                  class="com.auchan.refUniqueServices.infosProduits.bo.ElementNomenclature"
                  column="NSU_ENC"
                  outer-join="true"
              />

        </set>
...
</hibernate-mapping>

is it normal that this association doesn't generate the
"where NSU_DEN='450008' " i've declared in the mapping file?

imust be doing something wrong...
Thanks,

Anthony

emmanuel · **Posted:** Thu Apr 01, 2004 3:37 am

Isn't it used for the association table request ?

anthony · **Posted:** Thu Apr 01, 2004 3:54 am

in the generated sql i have the clause where defined in the collection :
where="NSU_VRN='30008' and DAT_FIN_L01 is null
but not he one defined in the global mapping file: where="NSU_DEN='450008'

Gavin said in another topic that it was normal since collections have their own "where" attribute, and for one-to-many this ok.
But for many-to-many it is impossible for me to define where="NSU_DEN='450008', it is not related to the association table :(

Balagan · **Posted:** Thu Nov 24, 2005 5:03 pm

Euh.... Anthony, what was the final word with this issue? I am a NHibernate user and it does look like the current (1.0.1) release works in the same way.
Big pb for me since my legacy db has an "Inactive" flag everywhere and all apps are supposed to ignore all inactive values.

The "where" element in class declaration seem to be ignored in most of collections and associations:
For example a Employee->User association

Class Employee:

Code:

<class name="Employee, MyApp" table="Employee" where="inactive=0" >
…
<many-to-one name="User" class="User, MyApp"
column="UserId" unique="true" not-null="false" >

Class User:

Code:

Would generate SQL:
SELECT… FROM Employee this LEFT OUTER JOIN Users user1_ ON this.UserId=user1_.id WHERE this.inactive=0
= user1_.inactive=0 is missing.
The User objects will be retrived ignoring the "inactive" flag on Users table. Similar behaviour with <bag>, <one-to-one> and others.

I wouldn't mind at all hacking NHib code, but I've seen posts from Gavin that seemed to indicate that this is by design (not quite the same case though).
[/code]

lesuako · **Joined:** Thu Jun 30, 2005 10:35 am **Posts:** 13

I have run into a few similar situations. What I have found is that the where clause at the <class> level is only used when you directly load that class (eg. session.get...). However, when a class is being loaded by an association, that clause gets ignored. Here's the interesting part though. When you define the <set> in the mapping file that refers to this class, you have the option of putting a where clause inside of 2 places--the <set> OR the <many-to-many>. If you want the where clause to filter the relationship table you need to put it into the <set>. However, if you want the where clause to filter on the opposite end class that the relationship table refers to then you put it in the <many-to-many>.

The really strange thing though, is that my xml editor complains about the where clause in the many-to-many element. According to the DTD it is not allowed. But lo and behold, it works perfectly for me.

Let me know if this helps.