[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-3356) Support for subselect (subquery) in from clause

[Chris Wilson (JIRA)]

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

Chris Wilson commented on HHH-3356:
-----------------------------------

Provided that you don't need to JOIN to a subselect, e.g. if you just want to further summarise the results of a query, you can use our SuperSelect custom query (http://rita.logscluster.org/browser/rita/src/org/wfp/rita/db/SuperSelect.java). It has a few dependencies that you can pull out from the RITA source, or just remove.

You can see some examples of its use here: http://rita.logscluster.org/browser/rita/src/org/wfp/rita/datafacade/DashboardDao.java, especially the DashboardQuery() constructor. The main process is:

* Create a criteria query for the inner query (subselect)
* Create a SuperSelect around it
* select() some columns from the inner query in the SuperSelect, e.g. by wrapping InnerQueryExpression, NonGroupedExpression and AggregateFunctionExpression objects around some of the inner query's Criterion objects
* Restrict result set rows with where()
* sort() the outer result set
* list() the SuperSelect to return the projected rows.

The getDispatchesByDateQuery() function uses SuperSelect to build a query like this:

{code:sql}
SELECT
  (0 - SUM(ship_ltu_weight * (ship_ltu_qty / line_ltu_qty))) AS dispatch_weight,
  (0 - SUM(ship_ltu_volume * (ship_ltu_qty / line_ltu_qty))) AS dispatch_volume,
  dispatch_month
FROM
  (SELECT
  SUM(dispatch.ltu_qty) AS ship_ltu_qty,
  line.ltu_qty AS line_ltu_qty,
  line.ltu_weight AS ship_ltu_weight,
  line.ltu_volume AS ship_ltu_volume,
  CAST(CONCAT(LEFT(journey.actual_dispatch_time, 7), "-01") AS DATE) AS dispatch_month
FROM
  movement AS dispatch
  INNER JOIN location AS origin
    ON  origin.location_site_id = dispatch.affected_location_site_id
    AND origin.id = dispatch.affected_location_id
  INNER JOIN site AS origin_site
    ON  origin_site.id = origin.parent_site_id
  INNER JOIN request_line AS line
    ON  line.request_site_id = dispatch.request_site_id
    AND line.id = dispatch.request_line_id
  INNER JOIN product
    ON  product.id = line.product_id
  LEFT OUTER JOIN product AS family
    ON  family.product_idnf = SUBSTR(product.product_idnf, 1, 3)
  INNER JOIN request
    ON  request.request_site_id = line.request_site_id
    AND request.id = line.request_id
  INNER JOIN project
    ON  project.id = request.project_id
  INNER JOIN country
    ON  country.id = project.country_id
  INNER JOIN site AS request_site
    ON  request_site.id = request.request_site_id
  LEFT OUTER JOIN contact AS sender
    ON  sender.owner_site_id = request.sender_site_id
    AND sender.id = request.sender_contact_id
  LEFT OUTER JOIN org AS sender_org
    ON  sender_org.id = sender.org_id
  LEFT OUTER JOIN org_type AS sender_org_type
    ON  sender_org_type.code = sender_org.org_type_code
  INNER JOIN shipment
    ON  shipment.shipment_site_id = dispatch.shipment_site_id
    AND shipment.id = dispatch.shipment_id
  INNER JOIN location AS destination
    ON  destination.location_site_id = shipment.destination_location_site_id
    AND destination.id = shipment.destination_location_id
  INNER JOIN site AS destination_site
    ON  destination_site.id = destination.parent_site_id
  INNER JOIN journey
    ON  journey.journey_site_id = shipment.shipment_site_id
    AND journey.id = shipment.journey_id
  LEFT OUTER JOIN vehicle_category
    ON  vehicle_category.id = journey.vehicle_category_id
WHERE dispatch.is_deleted = false
  AND shipment.is_deleted = false
  AND journey.is_deleted = false
  AND line.is_deleted = false
  AND request.is_deleted = false
  AND project.id = $P{Project_ID}
  AND origin_site.id = $P{Site_ID}
  AND journey.actual_dispatch_time IS NOT NULL
  AND journey.actual_dispatch_time > "2000-01-01"
  AND dispatch.movement_ledger_code = "S"
  AND journey.journey_type_code = "T"
GROUP BY
  line.ltu_qty,
  line.ltu_weight,
  line.ltu_volume,
  origin.location_site_id,
  origin.id,
  shipment.shipment_site_id,
  shipment.id,
  CAST(CONCAT(LEFT(journey.actual_dispatch_time, 7), "-01") AS DATE)) AS select_0
WHERE (ship_ltu_qty < 0)
GROUP BY
  dispatch_month
{code}

It should be quite easy to add support for multiple subselects, and joining them together, to get the kind of query that Arbi Sookazian was asking for. I just haven't needed it yet, so I haven't had a reason to implement it.

> Support for subselect (subquery) in from clause
> -----------------------------------------------
>
>                 Key: HHH-3356
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3356
>             Project: Hibernate Core
>          Issue Type: New Feature
>          Components: query-hql
>            Reporter: Geoffrey De Smet
>
> Many databases support subselects in the from clause, for example:
>   http://dev.mysql.com/doc/refman/5.0/en/unnamed-views.html
> And it's very handy.
> Native query's are sometimes a working workaround, but have many disadvantages, unlike HQL.
> Btw, I've searched through jira for over 20 minutes and didn't find an issue for this already. If there is one already, please give it a better title or link it to this issue :)

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