Hibernate Community • View topic - outer joins vs. subquery

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

outer joins vs. subquery

Page 1 of 1

[ 7 posts ]

Previous topic | Next topic

Author

Message

malraux

Post subject: outer joins vs. subquery

Posted: Sun Aug 23, 2009 5:13 pm

Newbie

Joined: Sun Aug 23, 2009 4:59 pm
Posts: 4

Hi all,

I'm tearing my hair out here.

I have a table of Issues, each of which has a Severity. I need to create a query counting the number of Issues per Severity (easy enough) with rows of 0 for Severities with no Issues (this is killing me). The latest problem is that named parameters do not seem to work in subqueries, and I need to trim the counts according to other criteria (such as i.status.id in (:status_List)).

Code:

select i.severity.id, count(i) from Issue i right outer join i.severity where i.status.id in (:status_List) group by i.severity.id;

The above doesn't return what I need, as the where clause filters everything, not just the Issues.

I've tried:

Code:

select s.id, (select count(i) from Issue i where i.severity.id = s.id and i.status.id in (:status_List)) from Severity s group by s.id;

The above gets me "Named parameter does not appear in Query".

I've tried joining to a subquery:

Code:

select s.id, count(i) from Severity s left outer join (from Issue i where i.severity.id = s.id and i.status.id in (:status_List)) group by s.id;

The above doesn't work because joining to a subquery is apparently not supported.

At this point my alternatives appear to be two:

1) munge the results after the fact to add in missing Severities (ugly, innit, but it would work)
2) drop to a SqlQuery, which is a whole world of hurt due to above mentioned other criteria, which can get quite complicated and are best described in HQL, not SQL... that's why I'm using Hibernate in the first place, eh? :-)

Any suggestions? Help would be greatly appreciated at this point.

Regards,
-scott

Top

latha1119

Post subject: Re: outer joins vs. subquery

Posted: Mon Aug 24, 2009 2:59 pm

Regular

Joined: Mon Jan 05, 2009 6:42 pm
Posts: 99
Location: IL

Hi Scott,
how does your Severity and Issue tables in the database look like? Please post the database columns in each of the table and if possible one or two records of data.
Also (my way) try building a SQL query that works and then a HQL/Criteria equivalent to it.

-Srilatha.

Top

malraux

Post subject: Re: outer joins vs. subquery

Posted: Mon Aug 24, 2009 3:38 pm

Newbie

Joined: Sun Aug 23, 2009 4:59 pm
Posts: 4

I can quite easily do this in SQL:

Code:

select s.s_id, (select count(*) from issue i where i.i_s_id = s.s_id and i.i_status in (2)) from severity s;
 s_id | ?column? 
------+----------
    1 |        0
    2 |        3
    3 |        0
    4 |        0

Or:

Code:

 select s.s_id, count(i_id) from severity s left outer join (select i_id, i_s_id from issue i where i.i_status in (2)) as foo on (s.s_id = foo.i_s_id) group by s.s_id;
 s_id | count 
------+-------
    1 |     0
    2 |     3
    3 |     0
    4 |     0

I can't figure out how to do either of those in HQL though.

Code:

  <class lazy="true" name="Issue" table="issue">
    <cache usage="read-write"/>
    <id column="i_id" name="id" unsaved-value="0">
      <generator class="sequence">
        <param name="sequence">issue_seq</param>
      </generator>
    </id>
    <many-to-one column="i_s_id" name="severity"/>
    <many-to-one column="i_status" name="status"/>
  </class>

Code:

  <class lazy="true" name="Severity" table="severity">
    <cache usage="read-write"/>
    <id column="s_id" name="id" unsaved-value="0">
      <generator class="sequence">
        <param name="sequence">severity_seq</param>
      </generator>
    </id>
    <property column="s_name" name="name"/>
  </class>

Code:

# select i_id, i_s_id, i_status from issue;
 i_id | i_s_id | i_status 
------+--------+----------
    1 |      1 |        1
    2 |      2 |        2
    3 |      2 |        2
    4 |      1 |        5
    5 |      2 |        5
    6 |      3 |        5
    7 |      3 |        5
    8 |      3 |        5
    9 |      2 |        2
   10 |      3 |        5
   11 |      1 |        1

Code:

# select s_id, s_name from severity;
 s_id |   s_name   
------+------------
    1 | 3 - High
    2 | 2 - Medium
    3 | 1 - Low
    4 | 0 - Info

Top

archetyp3

Post subject: Re: outer joins vs. subquery

Posted: Mon Aug 24, 2009 5:16 pm

Newbie

Joined: Mon Aug 24, 2009 4:59 pm
Posts: 2

I have the same issue on what you have now. You can use SubSelect or DetachedCriteria.

Top

latha1119

Post subject: Re: outer joins vs. subquery

Posted: Mon Aug 24, 2009 5:16 pm

Regular

Joined: Mon Jan 05, 2009 6:42 pm
Posts: 99
Location: IL

Scott,
I have mocked up your data,
here the SQL for the Requirement :-counting the number of Issues per Severity

select count(issue.i_id) issues ,severity.s_id
from severity severity
left join issue issue on (severity.s_id = issue.i_s_id)
group by severity.s_id

data output
issues s_id
3 1
4 2
4 3
0 4

now something like your requirement
select count(issue.i_id) issues ,severity.s_id
from severity severity
left join issue issue on (severity.s_id = issue.i_s_id and issue.i_status in (2))
group by severity.s_id

data output
issues s_id
0 1
3 2
0 3
0 4

Now the fun part is to convert this into HQL. let me know if you could not write the HQL i will give an attempt..

-Srilatha.

Top

malraux

Post subject: Re: outer joins vs. subquery

Posted: Mon Aug 24, 2009 5:34 pm

Newbie

Joined: Sun Aug 23, 2009 4:59 pm
Posts: 4

Srilatha,

Yes, those are essentially the same as one of the queries I posted above. :-)

Since HQL doesn't support joining to a subselect, it can't be duplicated that I know of. And since parameters in subselects don't work, I can't do the other one as well.

Regards,
-scott

Top

malraux

Post subject: Re: outer joins vs. subquery

Posted: Mon Aug 24, 2009 5:35 pm

Newbie

Joined: Sun Aug 23, 2009 4:59 pm
Posts: 4

archetyp3, can SubSelect be used in conjunction with a join?

Regards,
-scott

Top

Page 1 of 1

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