Left join fetch question

breidenr · **Posted:** Tue Dec 23, 2003 12:42 pm

This is more of a clarification...

If I am using "left join fetch" to eagerly load a collection of child objects in a find, I can't query against any of the child attributes, correct? For example, would this be illegal HQL?

Code:

from PurchaseOrderMetaData purchaseOrder
left join fetch purchaseOrder.actions action
where action.status = ?

If so, why?

Thanks.

Ryan

emmanuel · **Posted:** Tue Dec 23, 2003 1:12 pm

I think this is legal.

breidenr · **Posted:** Tue Dec 23, 2003 2:06 pm

Well, I have not has success getting this to work. Here is all the relevant stuff:

Code:

package webedi.mailbox.data;


import java.io.Serializable;
import java.util.SortedSet;
import java.util.TreeSet;

import org.apache.commons.lang.StringUtils;

import webedi.mailbox.service.document.PurchaseOrder;
import webedi.mailbox.service.document.Status;
import webedi.mailbox.service.vendor.Vendor;
import webedi.mailbox.util.MailboxDateConverter;


/**
 * @hibernate.class
 *         table="purchase_order"
 */
public class PurchaseOrderMetaData implements Serializable {
    
    
    private Integer purchaseOrderId;
    private Vendor vendor;
    private String storeNum;
    private String purchaseOrderNum;
    private String createdDate;
    private String mailbagId;
    private String storeType;
    private String poType;
    private String poFileDate;
    private String doNotShipBeforeDate;
    private String cancelIfNotShippedAfterDate;
    private SortedSet actions;

    
/*==================================================================================================
 * Constructors
==================================================================================================*/
    
    public PurchaseOrderMetaData(PurchaseOrder po, Vendor vendor) {
        setPurchaseOrderNum(po.getDocumentNumber());
        setStoreNum(po.getStoreNumber());
        setVendor(vendor);
        setCreatedDate(MailboxDateConverter.convert(po.getCreatedDate()));    
    }
    
    public PurchaseOrderMetaData() {
        // does nothing
    }

    
/*==================================================================================================
 * Public methods
==================================================================================================*/
    
    public boolean isCorporate() {
        return StringUtils.equals(poType, "POC");
    }
    
    public Status getStatus() {
        return getMostRecentAction().getStatus();
    }
    
    public PurchaseOrderAction getMostRecentAction() {
        if (getActions() == null || getActions().size() == 0) {
            throw new RuntimeException(
                    "Actions should never be empty - there may be a bug in the application.");           
        }
        return (PurchaseOrderAction) getActions().first();
    }
    
    public void addAction(PurchaseOrderAction action) {
        if (getActions() == null) {
            setActions(new TreeSet());
        }
        getActions().add(action);
    }


/*==================================================================================================
 * Property methods
==================================================================================================*/

    
    /**
     * @hibernate.id
     *     column="purchase_order_id"
     *     generator-class="common.external.hibernate.HiloGenerator"
     * 
     * @hibernate.generator-param
     *     name="tableName"
     *     value="purchase_order"
     * 
     * @hibernate.generator-param
     *     name="keysTable"
     *     value="key_counter"
     */
    public Integer getPurchaseOrderId() {
        return purchaseOrderId;
    }

    public void setPurchaseOrderId(Integer purchaseOrderId) {
        this.purchaseOrderId = purchaseOrderId;
    }

    /**
     * @hibernate.many-to-one
     *     column="vendor_id"
     */
    public Vendor getVendor() {
        return vendor;
    }

    public void setVendor(Vendor vendor) {
        this.vendor = vendor;
    }
    
    /**
     * @hibernate.property
     *     column="store_no"
     */
    public String getStoreNum() {
        return storeNum;
    }

    public void setStoreNum(String storeNum) {
        this.storeNum = storeNum;
    }
    
    /**
     * @hibernate.property
     *     column="purchase_order_no"
     */
    public String getPurchaseOrderNum() {
        return purchaseOrderNum;
    }

    public void setPurchaseOrderNum(String purchaseOrderNum) {
        this.purchaseOrderNum = purchaseOrderNum;
    }
    
    /**
     * @hibernate.property
     *     column="created_dtm"
     */
    public String getCreatedDate() {
        return createdDate;
    }

    public void setCreatedDate(String createdDate) {
        this.createdDate = createdDate;
    }
    
    /**
     * @hibernate.property
     *     column="mailbag_id"
     */
    public String getMailbagId() {
        return mailbagId;
    }

    public void setMailbagId(String mailbagId) {
        this.mailbagId = mailbagId;
    }
        
    /**
     * @hibernate.property
     *     column="store_type"
     */
    public String getStoreType() {
        return storeType;
    }

    public void setStoreType(String storeType) {
        this.storeType = storeType;
    }
    
    /**
     * @hibernate.property
     *     column="po_type"
     */
    public String getPoType() {
        return poType;
    }

    public void setPoType(String poType) {
        this.poType = poType;
    }
    
    /**
     * @hibernate.property
     *     column="po_file_date"
     */
    public String getPoFileDate() {
        return poFileDate;
    }

    public void setPoFileDate(String poFileDate) {
        this.poFileDate = poFileDate;
    }
    
    /**
     * @hibernate.property
     *     column="do_not_ship_before_date"
     */
    public String getDoNotShipBeforeDate() {
        return doNotShipBeforeDate;
    }
 
    public void setDoNotShipBeforeDate(String doNotShipBeforeDate) {
        this.doNotShipBeforeDate = doNotShipBeforeDate;
    }
       
    /**
     * @hibernate.property
     *     column="cancel_if_not_shipped_date"
     */
    public String getCancelIfNotShippedAfterDate() {
        return cancelIfNotShippedAfterDate;
    }

    public void setCancelIfNotShippedAfterDate(String cancelIfNotShippedAfterDate) {
        this.cancelIfNotShippedAfterDate = cancelIfNotShippedAfterDate;
    }
  
    /**
     * @hibernate.set
     *     table="purchase_order_action"
     *     lazy="false"
     *     cascade="all"
     *     sort="natural"
     * 
     * @hibernate.collection-key
     *     column="purchase_order_id"
     * 
     * @hibernate.composite-element
     *     class="webedi.mailbox.data.PurchaseOrderAction"
     */
    public SortedSet getActions() {
        return actions;
    }

    public void setActions(SortedSet actions) {
        this.actions = actions;
    }

}

Code:

package webedi.mailbox.data;


import java.io.Serializable;
import java.util.Date;

import common.util.DateUtil;
import webedi.mailbox.service.document.Status;


public class PurchaseOrderAction implements Serializable, Comparable {

    
    private Integer userNumber;
    private Date actionDate;
    private Status status;
    private Boolean verified;


/*==================================================================================================
 * Constructors
==================================================================================================*/
    
    public PurchaseOrderAction() {
        // does nothing
    }
    
    public PurchaseOrderAction(Status status) {
        this(status, null);
    }
    
    public PurchaseOrderAction(Status status, Integer userNumber) {
        this(status, userNumber, Boolean.FALSE);
    }
    
    public PurchaseOrderAction(Status status, Integer userNumber, Boolean verified) {
        setStatus(status);
        setUserNumber(userNumber);
        actionDate = new Date();
        setVerified(verified);
    }


/*==================================================================================================
 * Public methods 
==================================================================================================*/
    
    public boolean equals(Object obj) {
        return this.status.equals( ((PurchaseOrderAction) obj).status);
    }
    
    public int hashCode() {
        return status.hashCode();
    }

    public int compareTo(Object obj) {
        return this.status.compareTo( ((PurchaseOrderAction) obj).status);
    }
    
    public void markAsVerified() {
        setVerified(Boolean.TRUE);
    }
    
    public void markAsUnverified() {
        setVerified(Boolean.TRUE);
    }

    
/*==================================================================================================
 * Property methods 
==================================================================================================*/

    /**
     * @hibernate.property
     *     column="user_no"
     */
    public Integer getUserNumber() {
        return userNumber;
    }

    public void setUserNumber(Integer userNumber) {
        this.userNumber = userNumber;
    }
            
    /**
     * @hibernate.property
     *     column="action_dtm"
     */
    public Date getActionDate() {
        return DateUtil.copyDate(actionDate);
    }

    public void setActionDate(Date actionDate) {
        this.actionDate = DateUtil.copyDate(actionDate);
    }
        
    /**
     * @hibernate.property
     *     column="po_status_id"
     *     type="webedi.mailbox.data.StatusUserType"
     */
    public Status getStatus() {
        return status;
    }

    public void setStatus(Status status) {
        this.status = status;
    }
        
    /**
     * @hibernate.property
     *     column="verified_ind"
     *     type="common.external.hibernate.BooleanToIntUserType"
     */
    public Boolean getVerified() {
        return verified;
    }

    public void setVerified(Boolean verified) {
        this.verified = verified;
    }

}

Code:

<?xml version="1.0"?>

<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>
    <class
        name="webedi.mailbox.data.PurchaseOrderMetaData"
        table="purchase_order"
        dynamic-update="false"
        dynamic-insert="false"
    >

        <id
            name="purchaseOrderId"
            column="purchase_order_id"
            type="java.lang.Integer"
        >
            <generator class="common.external.hibernate.HiloGenerator">
                <param name="tableName">purchase_order</param>
                <param name="keysTable">key_counter</param>
            </generator>
        </id>

        <many-to-one
            name="vendor"
            class="webedi.mailbox.service.vendor.Vendor"
            cascade="none"
            outer-join="auto"
            update="true"
            insert="true"
            column="vendor_id"
        />

        <property
            name="storeNum"
            type="java.lang.String"
            update="true"
            insert="true"
            column="store_no"
        />

        <property
            name="purchaseOrderNum"
            type="java.lang.String"
            update="true"
            insert="true"
            column="purchase_order_no"
        />

        <property
            name="createdDate"
            type="java.lang.String"
            update="true"
            insert="true"
            column="created_dtm"
        />

        <property
            name="mailbagId"
            type="java.lang.String"
            update="true"
            insert="true"
            column="mailbag_id"
        />

        <property
            name="storeType"
            type="java.lang.String"
            update="true"
            insert="true"
            column="store_type"
        />

        <property
            name="poType"
            type="java.lang.String"
            update="true"
            insert="true"
            column="po_type"
        />

        <property
            name="poFileDate"
            type="java.lang.String"
            update="true"
            insert="true"
            column="po_file_date"
        />

        <property
            name="doNotShipBeforeDate"
            type="java.lang.String"
            update="true"
            insert="true"
            column="do_not_ship_before_date"
        />

        <property
            name="cancelIfNotShippedAfterDate"
            type="java.lang.String"
            update="true"
            insert="true"
            column="cancel_if_not_shipped_date"
        />

        <set
            name="actions"
            table="purchase_order_action"
            lazy="false"
            inverse="false"
            cascade="all"
            sort="natural"
        >

              <key
                  column="purchase_order_id"
              />

              <composite-element
                  class="webedi.mailbox.data.PurchaseOrderAction"
              >

        <property
            name="userNumber"
            type="java.lang.Integer"
            update="true"
            insert="true"
            column="user_no"
        />

        <property
            name="actionDate"
            type="java.util.Date"
            update="true"
            insert="true"
            column="action_dtm"
        />

        <property
            name="status"
            type="webedi.mailbox.data.StatusUserType"
            update="true"
            insert="true"
            column="po_status_id"
        />

        <property
            name="verified"
            type="common.external.hibernate.BooleanToIntUserType"
            update="true"
            insert="true"
            column="verified_ind"
        />

              </composite-element>

        </set>

        <!--
            To add non XDoclet property mappings, create a file named
                hibernate-properties-PurchaseOrderMetaData.xml
            containing the additional properties and place it in your merge dir.
        -->

    </class>

</hibernate-mapping>

Here is the method from my PurchaseOrderDao:

Code:

    public List findPurchaseOrders(String vendorId, final Status status) {
        
        LOGGER.debug("Finding purchase orders for vendor " + vendorId);
        
        Object[] values = new Object[] { vendorId, status.getId() };
        Type[] types= new Type[] { Hibernate.STRING, Hibernate.INTEGER };

        String query =
                "select purchaseOrder from PurchaseOrderMetaData purchaseOrder " +
                "left join fetch purchaseOrder.actions action " +
                "where purchaseOrder.vendor.vendorId = ? " +
                "and action.status = ? ";
        List orders = HibernateUtils.find(query, values, types);
        
        CollectionUtils.filter(orders, new Predicate() {
            public boolean evaluate(Object obj) {
                return ( (PurchaseOrderMetaData) obj).getStatus().equals(status);
            }
        });
        
        return orders;
    }

Which yields this error:

Code:

[ERROR] 12:01:31 SecureAction.handleError - common.exception.SystemException: net.sf.hibernate.QueryException: Incorrect query syntax [select purchaseOrder from webedi.mailbox.data.PurchaseOrderMetaData purchaseOrder left join fetch purchaseOrder.actions action where purchaseOrder.vendor.vendorId = ? and action.status = ? ]
        at common.external.hibernate.HibernateUtils.find(HibernateUtils.java:188)
        at webedi.mailbox.data.PurchaseOrderDaoImpl.findPurchaseOrders(PurchaseOrderDaoImpl.java:96)
        at webedi.mailbox.service.document.PurchaseOrderServicesImpl.findHeaders(PurchaseOrderServicesImpl.java:51)
        at webedi.web.action.vendor.PurchaseOrderListAction.getHeaders(PurchaseOrderListAction.java:21)
        at webedi.web.action.vendor.DocumentListAction.performSecurely(DocumentListAction.java:47)
        at webedi.web.action.SecureAction.findForward(SecureAction.java:50)
        at webedi.web.action.SecureAction.execute(SecureAction.java:34)
        at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
        at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
        at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
        at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
        at webedi.web.EdiActionServlet.service(EdiActionServlet.java:40)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
        at ...
        at org.apache.struts.action.RequestProcessor.doForward(RequestProcessor.java:1069)
        at org.apache.struts.action.RequestProcessor.processForwardConfig(RequestProcessor.java:455)
        at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:279)
        at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
        at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
        at webedi.web.EdiActionServlet.service(EdiActionServlet.java:40)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
        at ...
        at java.lang.Thread.run(Thread.java:536)
Caused by: net.sf.hibernate.QueryException: Incorrect query syntax [select purchaseOrder from webedi.mailbox.data.PurchaseOrderMetaData purchaseOrder left join fetch purchaseOrder.actions action where purchaseOrder.vendor.vendorId = ? and action.status = ? ]
        at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:168)
        at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:140)
        at net.sf.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:291)
        at net.sf.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:1501)
        at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1472)
        at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1462)
        at common.external.hibernate.HibernateUtils.find(HibernateUtils.java:185)
        ... 53 more
Caused by: java.lang.NullPointerException
        at net.sf.hibernate.hql.QueryTranslator.getPersister(QueryTranslator.java:329)
        at net.sf.hibernate.hql.QueryTranslator.getPersisterForName(QueryTranslator.java:310)
        at net.sf.hibernate.hql.QueryTranslator.renderSQL(QueryTranslator.java:492)
        at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:157)
        ... 59 more

Any thoughts?

Ryan

emmanuel · **Posted:** Tue Dec 23, 2003 2:32 pm

Using hibernate 2.1 ?
composite-element cannot be queried in Hibernate 2.0

breidenr · **Posted:** Tue Dec 23, 2003 3:30 pm

Yes. Currently using Hibernate 2.1-b6. Haven't upgraded to the release version yet, but that shouldn't matter, right?

michael · **Posted:** Tue Dec 23, 2003 3:39 pm

I am not so sure this is legal ... see http://www.hibernate.org/hib_docs/reference/html/query-language.html#query-language-s3

- .. should not be used in the where clause.

Wouldn't a normal left join initialize the collection, too?

breidenr · **Posted:** Tue Dec 23, 2003 4:03 pm

gloeglm wrote:

I am not so sure this is legal ... should not be used in the where clause.

Didn't think so. I was sort of posting just to confirm this.

Quote:

Wouldn't a normal left join initialize the collection, too?

Doesn't seem to. With the mappings listed above, the following HQL results in 1+N queries :-(

Code:

Object[] values = new Object[] { vendorId, status.getId() };
        Type[] types= new Type[] { Hibernate.STRING, Hibernate.INTEGER };

        String query =
                "select purchaseOrder from PurchaseOrderMetaData purchaseOrder " +
                "left join purchaseOrder.actions action " +
                "where purchaseOrder.vendor.vendorId = ? " +
                "and action.status = ? ";
        List orders = HibernateUtils.find(query, values, types);

Here is the log (evidence of the 1+N):

Code:

[DEBUG] 13:51:06 BatcherImpl - prepared statement get: select purchase0_.purchase_order_id as purchase_order_id, purchase0_.vendor_id as vendor_id, purchase0_.store_no as store_no, purchase0_.purchase_order_no as purchase4_, purchase0_.created_dtm as created_5_, purchase0_.mailbag_id as mailbag_id, purchase0_.store_type as store_type, purchase0_.po_type as po_type, purchase0_.po_file_date as po_file_9_, purchase0_.do_not_ship_before_date as do_not_10_, purchase0_.cancel_if_not_shipped_date as cancel_11_ from purchase_order purchase0_, purchase_order_action actions1_ where purchase0_.purchase_order_id=actions1_.purchase_order_id(+) and ((purchase0_.vendor_id=? )and(actions1_.po_status_id=? ))
[DEBUG] 13:51:06 BatcherImpl - preparing statement
[DEBUG] 13:51:06 StringType - binding '20138' to parameter: 1
[DEBUG] 13:51:06 IntegerType - binding '1' to parameter: 2
[DEBUG] 13:51:06 Loader - processing result set
[DEBUG] 13:51:06 IntegerType - returning '3' as column: purchase_order_id
[DEBUG] 13:51:06 Loader - result row: 3
[DEBUG] 13:51:06 Loader - Initializing object from ResultSet: 3
[DEBUG] 13:51:06 Loader - Hydrating entity: com.michaels.webedi.mailbox.data.PurchaseOrderMetaData#3
[DEBUG] 13:51:06 StringType - returning '20138' as column: vendor_id
[DEBUG] 13:51:06 StringType - returning '2723' as column: store_no
[DEBUG] 13:51:06 StringType - returning '900194' as column: purchase4_
[DEBUG] 13:51:06 StringType - returning '20031218' as column: created_5_
[DEBUG] 13:51:06 StringType - returning '000BBA' as column: mailbag_id
[DEBUG] 13:51:06 StringType - returning 'STOR' as column: store_type
[DEBUG] 13:51:06 StringType - returning 'POR' as column: po_type
[DEBUG] 13:51:06 StringType - returning '20031218' as column: po_file_9_
[DEBUG] 13:51:06 StringType - returning '20031218' as column: do_not_10_
[DEBUG] 13:51:06 StringType - returning '20040101' as column: cancel_11_

This occurs 80 more times, once for each purchase order

Code:

[DEBUG] 13:51:13 Loader - done processing result set (81 rows)
[DEBUG] 13:51:13 BatcherImpl - done closing: 0 open PreparedStatements, 0 open ResultSets
[DEBUG] 13:51:13 BatcherImpl - closing statement
[DEBUG] 13:51:13 Loader - total objects hydrated: 81
[DEBUG] 13:51:13 SessionImpl - resolving associations for [com.michaels.webedi.mailbox.data.PurchaseOrderMetaData#3]
[DEBUG] 13:51:13 SessionImpl - loading [com.michaels.webedi.mailbox.service.vendor.Vendor#20138]
[DEBUG] 13:51:13 SessionImpl - attempting to resolve [com.michaels.webedi.mailbox.service.vendor.Vendor#20138]
[DEBUG] 13:51:13 SessionImpl - resolved object in session cache [com.michaels.webedi.mailbox.service.vendor.Vendor#20138]
[DEBUG] 13:51:13 SessionImpl - collection not cached
[DEBUG] 13:51:13 SessionImpl - done materializing entity [com.michaels.webedi.mailbox.data.PurchaseOrderMetaData#3]

This happens 80 more times.

Code:

[DEBUG] 13:51:15 SessionImpl - initializing non-lazy collections
[DEBUG] 13:51:15 SessionImpl - initializing collection [com.michaels.webedi.mailbox.data.PurchaseOrderMetaData.actions#174]
[DEBUG] 13:51:15 BatcherImpl - about to open: 0 open PreparedStatements, 0 open ResultSets
[DEBUG] 13:51:15 BatcherImpl - prepared statement get: select purchase0_.user_no as user_no__, purchase0_.action_dtm as action_dtm__, purchase0_.po_status_id as po_statu4___, purchase0_.verified_ind as verified5___, purchase0_.purchase_order_id as purchase1___ from purchase_order_action purchase0_ where purchase0_.purchase_order_id=?
[DEBUG] 13:51:15 BatcherImpl - preparing statement
[DEBUG] 13:51:15 IntegerType - binding '174' to parameter: 1
[DEBUG] 13:51:15 Loader - result set contains (possibly empty) collection: [com.michaels.webedi.mailbox.data.PurchaseOrderMetaData.actions#174]
[DEBUG] 13:51:15 SessionImpl - uninitialized collection: initializing
[DEBUG] 13:51:15 Loader - processing result set
[DEBUG] 13:51:15 Loader - result row: 
[DEBUG] 13:51:15 IntegerType - returning '174' as column: purchase1___
[DEBUG] 13:51:15 Loader - found row of collection: [com.michaels.webedi.mailbox.data.PurchaseOrderMetaData.actions#174]
[DEBUG] 13:51:15 SessionImpl - reading row
[DEBUG] 13:51:15 IntegerType - returning null as column: user_no__
[DEBUG] 13:51:15 TimestampType - returning '22 December 2003 08:57:15' as column: action_dtm__
[DEBUG] 13:51:15 IntegerType - returning '1' as column: po_statu4___
[DEBUG] 13:51:15 IntegerType - returning '1' as column: verified5___
[DEBUG] 13:51:15 Loader - done processing result set (1 rows)
[DEBUG] 13:51:15 BatcherImpl - done closing: 0 open PreparedStatements, 0 open ResultSets
[DEBUG] 13:51:15 BatcherImpl - closing statement
[DEBUG] 13:51:15 SessionImpl - collection fully initialized: [com.michaels.webedi.mailbox.data.PurchaseOrderMetaData.actions#174]
[DEBUG] 13:51:15 SessionImpl - 1 collections initialized

This happens 80 times. Yuck. So...is it possible to find a list of objects using criteria found in child records *and* eagerly load these children with one query?

Ryan

michael · **Posted:** Tue Dec 23, 2003 4:08 pm

I can't think of a really good sollution yet ... what does using both left join and left join fetch in the same query do?

emmanuel · **Posted:** Wed Dec 24, 2003 4:49 am

gloeglm wrote:

I am not so sure this is legal ... see http://www.hibernate.org/hib_docs/reference/html/query-language.html#query-language-s3

- .. should not be used in the where clause.

This is doable for entity-entity association for sure, I use that.

Code:

select parent from sample.Parent  parent left join fetch parent.children child where child.label = 'label'

What I'm not sure is this capability for collection of values, but it should. Are you sure there isn't something else ?

gavin · **Posted:** Wed Dec 24, 2003 6:39 am

This should work. But it is "strange" to use the FETCH clause together with a WHERE condition.

You should use:

Code:

select purchaseOrder 
from PurchaseOrderMetaData purchaseOrder
left join purchaseOrder.actions action
left join fetch purchaseOrder.actions
where action.status = ?

or else the returned collection does not have all its elements!

danz8086 · **Joined:** Tue Mar 02, 2004 6:17 pm **Posts:** 20

Hi, Gavin
I have a question related to this - would appreciate your clarification.

Quote:

This should work. But it is "strange" to use the FETCH clause together with a WHERE condition.

....
or else the returned collection does not have all its elements!

So, if I DO want to retrieve only PART of the child collection's items, can I assume that it is "supported by Hibernate 2.1.x" to use the query below?

Code:

from PartItem item inner join item.itemScopes scope
where item.issueId=? and scope.affectedCustomer=?

Also, your example intended to return a Full collection using the second "join" with "fetch". Since I don't want the full collection, is there any better way other than "from ... join fetch ... where" in my scenario?

Thansk in advance,
Dan

Here is the problem I need to solve:

Code:

class Issue {
  private Long id;
  private int priority;
  private String bySupplier;
  private List partItems;
}

class PartItem {
  private int serialNum;
  private Long issueId;
  private List itemScopes;
}

class ItemScope {
  private Long itemId;
  private String affectedCustomer;
  private String affectedPlant;
  private String affectedProduct;
}

class IssueDao{
  public List getIssueForCustomer(String customerName){  }
}

Issue can have items affecting all customers, but when a single customer read the issue, I have to retrieve the PartItems so that

lovkiys · **Joined:** Mon Sep 22, 2003 5:18 am **Posts:** 28

With Hibernate3 it is possible to apply filter on collection. So, this would partially resolve problem of loading subset from childs collection but wont move you from the problem if 1+N select for this child collection (since left join fetch means that you got cartesian production, i.e parent * child matrix)