-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 posts ] 
Author Message
 Post subject: Subselect on collection element attribute
PostPosted: Fri Dec 12, 2003 12:46 pm 
Beginner
Beginner

Joined: Wed Aug 27, 2003 8:55 am
Posts: 28
Location: Dallas, TX
OK. Here is a high level look at my objects. I have a PurcahseOrderMetaData object that contains a SortedSet of PurchaseOrderAction objects. These actions represent an action taken by the user (viewed it, created an invoice for it, etc). Here is snippet of the classes and their mappings with all the extraneous properties removed:

Code:
/**
* @hibernate.class
*         table="purchase_order"
*/
public class PurchaseOrderMetaData implements Serializable {
   
   
    private Integer purchaseOrderId;
    private Vendor vendor;
    private SortedSet actions;

   
    public Status getStatus() {
        return getMostRecentAction().getStatus();
    }
   
    public PurchaseOrderAction getMostRecentAction() {
        return (PurchaseOrderAction) getActions().first();
    }

    /**
     * @hibernate.id
     *     column="purchase_order_id"
     *     generator-class="assigned"
     */
    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.set
     *     table="purchase_order_action"
     *     lazy="false"
     *     cascade="all"
     *     sort="natural"
     *
     * @hibernate.collection-key
     *     column="purchase_order_id"
     *
     * @hibernate.composite-element
     *     class="PurchaseOrderAction"
     */
    public SortedSet getActions() {
        return actions;
    }

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

}


Code:
public class PurchaseOrderAction implements Serializable, Comparable {

   
    private Status status;


    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);
    }

       
    /**
     * @hibernate.property
     *     column="po_status_id"
     *     type="StatusUserType"
     */
    public Status getStatus() {
        return status;
    }

    public void setStatus(Status status) {
        this.status = status;
    }

}


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="PurchaseOrderMetaData"
        table="purchase_order"
        dynamic-update="false"
        dynamic-insert="false"
    >

        <id
            name="purchaseOrderId"
            column="purchase_order_id"
            type="java.lang.Integer"
        >
            <generator class="assigned">
            </generator>
        </id>

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

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

              <key
                  column="purchase_order_id"
              />

              <composite-element
                  class="PurchaseOrderAction"
              >


        <property
            name="status"
            type="StatusUserType"
            update="true"
            insert="true"
            column="po_status_id"
        />

       
              </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>


What I am trying to do is create a query that finds all POs for a given status. This is determined by finding POs whose action with the "greatest" status (a status is represented by an integer in the DB - successive statuses are greater than the previous) equals the given status. So, I think I need a subselect against the action table with a max clause. At least, this is how it is being done currently with SQL (I am trying to convert this to Hibernate).

My currect query looks like this:

Code:
public static List findUnverifiedPurchaseOrders(Status status) throws SQLException {
   
    Object[] values = new Object[] { status.getId() };
    Type[] types= new Type[] { Hibernate.INTEGER, Hibernate.INTEGER };
    String query =
            "select purchaseOrder from PurchaseOrderMetaData purchaseOrder " +
            "join purchaseOrder.actions action " +
            "where action.status = ? ";
   
    return HibernateUtils.find(query, values, types);
}


This pulls back all POs that have any action with the given status. I need to pull back POs whose action with the "greatest" status is equal to the given status. I have looked at several HQL examples in the docs and forum, but have not found a solution. I hope I provided enough info. Thanks for the help.

Ryan


Top
 Profile  
 
 Post subject:
PostPosted: Fri Dec 12, 2003 1:42 pm 
Hibernate Team
Hibernate Team

Joined: Sun Sep 14, 2003 3:54 am
Posts: 7256
Location: Paris, France
Try
Code:
select purchaseOrder from PurchaseOrderMetaData purchaseOrder join purchaseOrder.actions action where action.status = (select max(subaction.status) from PurchaseOrderMetaData subPurchaseOrder join subPurchaseOrder.actions subaction)";

_________________
Emmanuel


Top
 Profile  
 
 Post subject:
PostPosted: Fri Dec 12, 2003 6:01 pm 
Beginner
Beginner

Joined: Wed Aug 27, 2003 8:55 am
Posts: 28
Location: Dallas, TX
epbernard wrote:
Try
Code:
select purchaseOrder from PurchaseOrderMetaData purchaseOrder join purchaseOrder.actions action where action.status = (select max(subaction.status) from PurchaseOrderMetaData subPurchaseOrder join subPurchaseOrder.actions subaction)";


This doesn't seem to work. I only one purchase order has a status of "3", and all others was the status of "2", this will only pull back orders with a status of "3" or more, even if I am looking for status = "2". This is because it is comparing an action's status against the max status value for *all* actions. I only want to compare it against the max value for actions with the same purchase order.

Basically, the SQL I need should look like:

Code:
SELECT purchase_order.purchase_order_id, purchase_order_action.po_status_id,
FROM purchase_order, purchase_order_action
WHERE purchase_order.purchase_order_id = purchase_order_action.purchase_order_id
AND purchase_order_action.po_status_id = ?
AND purchase_order_action.po_status_id = (
    SELECT max(po_status_id)
    FROM purchase_order_action
    WHERE purchase_order_id = purchase_order.purchase_order_id
)


This is essentially what we have now. The first clause joins the order and action tables. The second clause limits the select to the status for which we are looking. The subquery ensures that this status is the "greatest" status for that order. Notice that the subquery joins with the *outer* order table.

Perhaps what I am asking for is crazy. Basically, an order's status is derived from its actions (1-M relationship). Which ever action associated with an order has the "greatest" (and therefore, most current) status is that order's status. I want to be able to query by an order's status.

Thanks again.

Ryan


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.