aspose file tools*
The moose likes Object Relational Mapping and the fly likes SQL to HQL (Having issue in Left Outer Join) Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "SQL to HQL (Having issue in Left Outer Join)" Watch "SQL to HQL (Having issue in Left Outer Join)" New topic
Author

SQL to HQL (Having issue in Left Outer Join)

Vikas Kapoor
Ranch Hand

Joined: Aug 16, 2007
Posts: 1374
SQL :


Line 1 is easy to convert in HQL.
Line 3 doesn't need to be converted to HQL.
How can I convert Line 2?

Cross Post
John Bengler
Ranch Hand

Joined: Feb 12, 2009
Posts: 133
Hi Vishal,

I'm afraid you have quite a problem here:

Note that HQL subqueries can occur only in the select or where clauses.


Taken from chapter 14.13 of the Hibernate reference:

Hibernate Reference

So you will have to rewrite your query without a subselect - or with the subselect in the select or in the where clause.

If anyone knows a workaround I'm highly interessted, too.


John
Emanuel Kadziela
Ranch Hand

Joined: Mar 24, 2005
Posts: 186
Is there any direct relationship between A and B? Is it expressed in Java, not just in DB? As far as I know, HQL likes property based joins, like Entity A has a field "private B b" and the join is select * from A as a left outer join a.b as B ....
Rahul Babbar
Ranch Hand

Joined: Jun 28, 2008
Posts: 210
Vishal Pandya wrote:SQL :


Line 1 is easy to convert in HQL.
Line 3 doesn't need to be converted to HQL.
How can I convert Line 2?


If you want to use Left Outer Join syntax of hibernate, you do not need to use the "On Clause"....hibernate automatically creates this clause based on the relationship between the two mapping files(hbms).

If you do not have a mapping, see whether there should be one..mostly you will find that it should be...

If you decide that there should not be any mapping....and you want to use Left Outer Join in Hibernate, there is one workaround which i know.



Please see here where it was discussed.

However, you should always prefer relationships in mappings unless you have a strong reason to avoid them.


Rahul Babbar
Vikas Kapoor
Ranch Hand

Joined: Aug 16, 2007
Posts: 1374
Emanuel Kadziela wrote:Is there any direct relationship between A and B? Is it expressed in Java, not just in DB? As far as I know, HQL likes property based joins, like Entity A has a field "private B b" and the join is select * from A as a left outer join a.b as B ....

Yes there is relationship between two tables in DB and hence it is also reflecting in hbm and pojo.

Here important thing is, I want A X B. There is a direct relationship between A and B as I already mentioned. But I want to apply filter on B table and then I want A X B(filtered).X denotes left outer join.

Now if I do something like,

then it would consider all the records of B while applying the join but I want only few records to be considered for join means I have to apply filter before join occurs.

and not mention the difference between,

John Bengler
Ranch Hand

Joined: Feb 12, 2009
Posts: 133
You're right, as I wrote in my first reply I'm interessted in a real workaround, too, because I encountered the same problems.

And rewriting the query in a way that does exactly the same is something between hard and impossible.

Even this would not return the desired result:



John
Rahul Babbar
Ranch Hand

Joined: Jun 28, 2008
Posts: 210
John Bengler wrote:You're right, as I wrote in my first reply I'm interessted in a real workaround, too, because I encountered the same problems.

And rewriting the query in a way that does exactly the same is something between hard and impossible.

Even this would not return the desired result:



John



Please see here for a solution i mentioned above.
It does exactly the work of a Outer Join in hibernate without using the Join clause.
Vikas Kapoor
Ranch Hand

Joined: Aug 16, 2007
Posts: 1374
Rahul, It is not possible to convert your solution into HQL. and if you read my last post carefully I have relationship between two tables.
Rahul Babbar
Ranch Hand

Joined: Jun 28, 2008
Posts: 210
The 'workaround' solution which i mentioned was only when you want to have a Outer Join using hibernate but do not have a relationship between the tables...
Vikas Kapoor
Ranch Hand

Joined: Aug 16, 2007
Posts: 1374
Anybody?
Rahul Babbar
Ranch Hand

Joined: Jun 28, 2008
Posts: 210
Vishal Pandya wrote:
Now if I do something like,

then it would consider all the records of B while applying the join but I want only few records to be considered for join means I have to apply filter before join occurs.

and not mention the difference between,



Why would you want to prefer one approach over the other...
As far as i understand, both these queries will get the same result...and will be of the same cost to the DB....!!!
Yes....you can verify it by running Explain Plan on the query.....

Today DBs dont execute the SQL in the same way as your SQL.....DBs can choose to modify the query to appropriate one(which will be better performing and will be effectively be same.)..

Also you would be mistaken if you think "A a left outer join a.b " to "consider all the records of B while applying the join "....

It will not.....The DB does not join the tables initially....but instead filters the row using the where clause, after that i starts to Join the tables.....so that both the above ways are equal performing in terms of cost to the DB...

The bottom line is dont try to second guess as to what exactly the DB will be doing in this case...and both the queries are equally valid.....and have the same cost for the DB.....although given a choice i would prefer to use the second one as it is cleaner and does not include an inner select.....
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL to HQL (Having issue in Left Outer Join)