Hello,

I have a query I have written in SQL that I can't figure out how to translate into HQL properly. I have a main table (PACKAGE) that is left-joined to two tables (IMAGE and EMAIL). The object modelling for this is that a Package object contains a collection of Image objects and a collection of Emal objects.

The goal is to produce a report showing how many activated emails and images are in each package. The count of the number of images is broken down by the dimensions of the images within the package. A row from the returned query, for example, should look as follows:



The sql query I execute is as follows:



The problem is that I can't determine if in HQL it is possible to join on the subqueries. That is, I would like my HQL to be something like "...from Package package left join package.images left join package.email", but wit the additional restriction that the join should only occur for activated items; however, I can't determine the correct syntax, if any, to support this.

I also considered an alternate approach using a WHERE clause, but I can't just look for items where i.is_activated=1 or e.is_activated=1 because that would remove all those Packages which contained no activated images or email from the result entirely.

Any ideas?

No responses yet... could one of the Hibernate developers please let me know if it is possible to perform outer joins on subqueries?

Oops - just realized I had some typos in the SQL. The correct SQL statement is:



This is the SQL statement that I can't translate into HQL.

We don't support subqueries in the ON clause (actually we don't have an ON clause at all). Try using createSQLQuery().

Thanks for the response, Gavin. I had considered using createSQLQuery(), but the problem I ran into is that one row in the ResultSet does not map to one row in the finished report.

In the final report one line item contains the number of images of each size, but you can see that those dimensions aren't known until after the query is performed. Due to this, I have to iterate through the results of find() or createSQLQuery() and put all items with the same package id into one ReportLineItem object.

My understanding of createSQLQuery() is that you can only use it when all of the returned columns on a given row map to the fields of a single object. Given the above explanation, it seems there is no way I can do this. Is this perhaps a case where I have to fall back on native JDBC?[/i]

I don't understand.

I'll try to clarify. The final report will be in the form of an HTML table. It will have the following columns:

package_name, email_count, 120x200_count, 130x90_count...etc.

with one column for every set of dimensions found in the database.

The email_count will simply be the number of emails contained in that package. The number of images however, will be broken down according to their dimensions. These dimensions are pulled from the LENGTH and WIDTH columns of the IMAGE table, though; because they are dynamically determined by querying the database, they can't be used as columns in the result set. Instead, any hibernate function (or sql resultSet, for that matter) will return a List where returned rows will look like:

package_id->1, email_count->3, width->120, height->200, image_count->2
package_id->1, email_count->3, width->130, height->90, image_count->1
package_id->1, email_count->3, width->220, height->100, image_count->5
package_id->2, email_count->0, width->120, height->80, image_count->3
etc.

Then, each ReportRow object would contain all the information pertaining to a given package_id. In the above example, the first ReportRow object would contain packageID=1, emailCount=3, and a Map that contains {120x200 =>2, 130x90=>1, 220x100=>5}.

My understanding of createSQLQuery(), however, was that each row returned by the query must map exactly to all the fields of one object. You can see from the above example, however, that I wouldn't have all the fields of the ReportRow object corresponding to packageID=1 until I looked at the first four items in the List.

Am I mistaken, or does this rule out the use of createSQLQuery() in this instance?