mapping: one-to-many WHERE property = FALSE

HUMPPA · **Joined:** Tue Sep 08, 2009 9:49 am **Posts:** 23

Hi!

Hibernate 3.2.

Maybe I'm using the wrong syntax, but the SQL command hibernate generates out of the following mapping is wrong and does not work.
I don't want the editions to join where the column optout is false (boolean column).

Code:

...
  <bag name="editions" table="edition" where="optout=false">
     <key column="festivalid"></key>
     <one-to-many class="hibr.Edition"/>
  </bag>
...

where block in the mapping is translated to:

Code:

SELECT ..... where ( editi0_.optout=editi0_.false)

Any ideas?

Greetings
Humppa!

Froestel · **Posted:** Fri Sep 18, 2009 6:31 pm

Hi HUMPPA (again ;-) ),

check, how your DBMS handles Boolean values - in a lot of cases these are mapped to some Integer type, i.e., having 0 for FALSE and 1 or anything else for TRUE. You are doing plain SQL in this place so you need to adapt the syntax to your DBMS.

CU
Froestel

HUMPPA · **Joined:** Tue Sep 08, 2009 9:49 am **Posts:** 23

Yo Mr. Froestel,

thx for your help (again ;-) ),

I use PostgreSQL 8.4 btw.

On page 113 of the hibernate reference they write that it's plain SQL code to go into the <bag ... where="<SQL>"> condition.

Quote:

where (optional): specifies an arbitrary SQL WHERE condition that is used when retrieving or removing the collection. This is useful if the collection needs to contain only a subset of the available data.

I already tried several variations like what you suggested, replacing false by 0. which got 'rendered' to

Code:

SELECT ..... where ( editi0_.optout=0)

Unfortunately PostgreSQL doesn't interpret 0 as false.
I've also tried

Code:

where="optout=false"
where="optout=FALSE"
where="optout<>true"
where="optout is false"
where="optout=#false"
where="optout='false'"
where="optout=0"
where="optout=1"

They all did not work.
The generated code was always like that one (except for 0 and 1)

Code:

SELECT ..... where ( editi0_.optout=editi0_.false)

If I replace editi0_.false by false and send it directly via pgAdminIII to the DB it works fine.

Code:

SELECT ..... where ( editi0_.optout=false)

Maybe it's a bug? In my mind it looks like Hibernate thinks that false is a column of the edition table. Or maybe there is a special syntax or escape sequence needed.

Greetings
Humppa!

HUMPPA · **Joined:** Tue Sep 08, 2009 9:49 am **Posts:** 23

As a workaround I now use the 'Custom SQL for loading' technique.

Code:

...
  <bag name="editions" table="edition" where="optout=false">
     <key column="festivalid"></key>
     <one-to-many class="hibr.Edition"/>
     <loader query-ref="CustomEditionLoader"/>
  </bag>

 </class>
 
<sql-query name="CustomEditionLoader">
   <load-collection alias="e" role="hibr.Festival.editions"></load-collection>
   <![CDATA[
      SELECT {e.*} FROM edition e WHERE e.festivalid = :id AND e.optout = false 
   ]]>
</sql-query>