Hibernate Community • View topic - Creating an inner join to the same table

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

Creating an inner join to the same table

Page 1 of 1

[ 3 posts ]

Previous topic | Next topic

Author

Message

jrobertsz66

Post subject: Creating an inner join to the same table

Posted: Sat Mar 29, 2014 1:24 am

Newbie

Joined: Sat Mar 29, 2014 1:21 am
Posts: 1

Hi,

How do you create an inner join to the same table using Criteria.createAlias or Criteria.createCriteria?

Basically, I have tried this:

Code:

            ProjectionList pList = Projections.projectionList();
            pList.add(Projections.groupProperty("orderBaseId"));
            
            
            session = HibernateSessionFactory.currentSession();
            Criteria crit = session.createCriteria(QuoteMaster.class);
            Criteria c = crit.createCriteria("QuoteMaster", CriteriaSpecification.INNER_JOIN);
            c.setProjection(pList);

But I get this error:

Code:

could not resolve property: this

Basically, I am trying to replicate this SQL query but I do not want to use the in keyword, I want to use an inner join just like in my query here:

Code:

SELECT * FROM
sys_quote_master AS g1
INNER JOIN (SELECT order_base_id, order_id
      FROM sys_quote_master where order_base_id="010313-6063" group by order_base_id order by order_id asc) AS g2
ON g2.order_id = g1.order_id;

Top

andrew.sc.syd

Post subject: Re: Creating an inner join to the same table

Posted: Sun Jun 08, 2014 7:41 pm

Newbie

Joined: Mon Dec 23, 2013 6:04 am
Posts: 1

jrobertsz66 wrote:

Hi,

Basically, I am trying to replicate this SQL query but I do not want to use the in keyword, I want to use an inner join just like in my query here:

Code:

SELECT * FROM
sys_quote_master AS g1
INNER JOIN (SELECT order_base_id, order_id
      FROM sys_quote_master where order_base_id="010313-6063" group by order_base_id order by order_id asc) AS g2
ON g2.order_id = g1.order_id;

Did you find a solution ? I'm having a similar problem. In my case, I don't even need an inner join to the same table. Rather, the challenge is to use a Select statement in the FROM list of tables. (It is required to make MS-SQL do an Order By on a column which is not returned by a Select Distinct). I just can't get the syntax right. In fact, I'm not sure at all whether the HQL parser recognises the syntax FROM ( SELECT ... ) AS TABLE

In practical terms, I solved the problem by dropping to the level of native SQL. But then, I had to manually map the result set to objects, with a ResultSetMaper. The number of source lines grew by a factor of 3, so I'm looking for a more elegant solution.

Andrew

Top

DavideD

Post subject: Re: Creating an inner join to the same table

Posted: Mon Jun 09, 2014 5:18 am

Hibernate Team

Joined: Fri Sep 09, 2011 3:18 am
Posts: 295

Hi,
from the documentation (http://docs.jboss.org/hibernate/orm/4.3/manual/en-US/html_single/#queryhql-subqueries):

Quote:

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

Maybe the discussion at https://forum.hibernate.org/viewtopic.php?p=2389790 and the documentation paragraph about DetachedCriteria and subqueries (http://docs.jboss.org/hibernate/orm/4.3/manual/en-US/html_single/#querycriteria-detachedqueries) might be of help.

Cheers,
Davide

Top

Page 1 of 1

[ 3 posts ]

Board index » Hibernate & Java Persistence » Hibernate Users

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