Hi,

I am trying to implement soft deletes in my system. Instead of physically deleting records, the column fld_record_status is set to 'D'. Every table has a fld_record_status column.
In my system. I have a tbl_patient table corresponding to Patient.java.
and a tbl_visit table corresponding to Visit.java.
There is a one-to-many relationship between Patient and Visits.
To retrieve only Patient and visit records which are not deleted I have included a "where clause" in the class level. I have also included a "where clause" in the hibernate bag for visits, such that it retrieves only visits which are not deleted for each patient. See mapping file.

Hibernate version: 2.1.7c
Database Version : mysql4.1.7
Patient.hbm.xml
<hibernate-mapping
>
<class
name="org.appfuse.model.Patient"
table="tbl_patient"
dynamic-update="false"
dynamic-insert="false"
select-before-update="false"
optimistic-lock="version"
where="fld_record_status!='D' "
>

<id
name="patientKey"
column="key_patient"
type="java.lang.Long"
>
<generator class="com.mdt.persistence.hibernate.pkpool.PKFactory">
<!--
To add non XDoclet generator parameters, create a file named
hibernate-generator-params-Patient.xml
containing the additional parameters and place it in your merge dir.
-->
</generator>
</id>

<bag
name="visits"
table="tbl_visit"
lazy="false"
inverse="true"
cascade="delete"
order-by="fld_date_created DESC"
where="fld_record_status!='D' "
>

<key
column="key_patient"
>
</key>

<one-to-many
class="org.appfuse.model.Visit"
/>

</bag>

Visit.hbm.xml

<hibernate-mapping
>
<class
name="org.appfuse.model.Visit"
table="tbl_visit"
dynamic-update="false"
dynamic-insert="false"
select-before-update="false"
optimistic-lock="version"
where="fld_record_status!='D' "
>

<id
name="visitKey"
column="key_visit"
type="java.lang.Long"
>
<generator class="com.mdt.persistence.hibernate.pkpool.PKFactory">
<!--
To add non XDoclet generator parameters, create a file named
hibernate-generator-params-Visit.xml
containing the additional parameters and place it in your merge dir.
-->
</generator>
</id>

<bag
name="charges"
table="tbl_charge"
lazy="false"
inverse="true"
cascade="delete"
order-by="fld_date_created DESC"
where="fld_record_status!='D' "
>

<key
column="key_visit"
>
</key>

<one-to-many
class="org.appfuse.model.Charge"
/>

</bag>

<many-to-one
name="patient"
class="org.appfuse.model.Patient"
cascade="none"
outer-join="auto"
update="true"
insert="true"
access="property"
column="key_patient"
/>





HQL query is:

SELECT p FROM org.appfuse.model.Patient AS p LEFT OUTER JOIN p.visits AS v GROUP BY p.patientKey WHERE p.assignedTo.entityKey = ? or p.ownerPhysician.entityKey = ? ORDER BY p.firstName ASC




Now when I run the above HQL query, an incorrect sql query is generated. Instead of appending the "where clause" in the join condition, it is appending it to the main where clause.

The correct query that should be generated is

select * from tbl_patient patient0_ left outer join tbl_visit visits1_ on (patient0_.key_patient=visits1_.key_patient and visits1_.fld_record_status!='D' ) where patient0_.fld_record_status!='D' and ((patient0_.key_assigned_to=? )or(patient0_.key_owner_physician=? )) group by patient0_.key_patient order by patient0_.fld_first_name ASC[/b]

Also notice the double where clause.

The generated SQL (show_sql=true):

select * from tbl_patient patient0_ left outer join tbl_visit visits1_ on patient0_.key_patient=visits1_.key_patient where patient0_.fld_record_status!='D' and visits1_.fld_record_status!='D' and visits1_.fld_record_status!='D' and ((patient0_.key_assigned_to=? )or(patient0_.key_owner_physician=? )) group by patient0_.key_patient order by patient0_.fld_first_name ASC

Is this a bug in hibernate?? Is there any other way, how I can get rows which are not deleted?

Any help will be appreciated...

Thanks

matetn