[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-3877) "Left join Fetch" "With Clause" Required

[Sandeep Vaid (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3877?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=33106#action_33106 ] 

Sandeep Vaid commented on HHH-3877:
-----------------------------------

I think if we don;t want to allow "with clause" with fetch, we should allow filters in <one-to-one> association:
Hibernate says, "With clause" with Fetch is not allowed, use Filters.

Consider this scenatio :
    I have one-to-one relation between Product and ProductBasic BUT at database level i have one-to-many between 
    PRODUCT and PRODUCTBASIC tables as history is mainted in the same table :
    
    PRODUCT ----> PID (PK)
    PRODUCTBASIC ---> PID (FK), USECODE forms Composite-key
    			
    PRODUCT
     PID	PNAME	
      1		Pen
    
    PRODUCTBASIC
     PID 	USECODE		STARTTIME		ENDTIME
     1		  100	       06-MAY-2009 11:00	07-MAY-2009 10:00
     1		  100	       07-MAY-2009 10:00	31-DEC-9999 00:00
     
     Now while querying for ProductBasic, i will always fetch the row which satisfies the condition STARTTIME<CURRENTTIME<ENDTIME
     and this will surely return me only one row.
     
     Ideally the generated sql query should be :
     
     select * from  PRODUCT product  left outer join PRODUCTBASIC productbasic  on '100'=productbasic.USETYPE 
        and product.PID=productbasic.PID 
        and (
         productbasic.STARTTIME<'09-MAY-2009 10:00' 
         and '09-MAY-2009 10:00'<productbasic.ENDTIME
        ) 
      where
  product.PID='1'

> "Left join Fetch" "With Clause" Required
> ----------------------------------------
>
>                 Key: HHH-3877
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3877
>             Project: Hibernate Core
>          Issue Type: Improvement
>    Affects Versions: 3.3.1
>            Reporter: Sandeep Vaid
>            Assignee: Diego Plentz
>
> Product and ProductBasic has one-to-one relationship
> Product ---> ProductID <<PK>>
> ProductBasic ----> ProductId, useCode, StartTime as composite key.....
>   this startTime's value will be known as runtime.
> Now i want to define mapping between Product & ProductBasic, how will i do it ?
> Moreover suppose i define their mapping as product.productID = productBasic.productID and useCode='a' as :
> Product.hbm.xml: 
>   <one-to-one name="productBasic" class="ProductBasic" cascade="save-update" property-ref="activeProduct" lazy="proxy" constrained="true">
>     <formula>'008'</formula>
>     <formula>PRODUCTID</formula>
>  </one-to-one>
>  
>  
>  ProductBasic.hbm.xml as :
>  <properties name="activeProduct">
>    <property  name="useCode" column="USECODE"></property>
>    <many-to-one name="productBO" class="ProductBO" column="PRODUCTID" insert="false" update="false"/> 
>   </properties>
> How can i specify startTime value at runtime (say in eager fetching)? I can't use "inner join fetch" along "with clause".. ?
> Or i want to specify a condition in fetch join that startTime<currentTime<endtime. the value of currenttime will be known
>  at runtime...How to make this work?

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira