Subquery alias help!

alwang · **Joined:** Sat Jun 17, 2006 9:04 pm **Posts:** 4

I have two tables, a "site" table for websites and a "health" table for the website health status recorded at the certain intervals. I need to query for currently active websites (status: up). This is the SQL I have:

select s.* from site s left join health h on s.id = h.site
where h.status = ? and h.time_of_last_update =
(select max(q.time_of_last_update) from health q
where q.site = s.id)

I can run this query fine on MySQL (5.0) console. But when I used it with hiberbnate (2.1.8). I had to put it in curly braces as this,

select {s.*} from site {s} left join health {h} on {s}.id = {h}.site
where {h}.status = ? and {h}.time_of_last_update =
(select max({q.time_of_last_update}) from health {q}
where {q}.site = {s}.id)

and fill the {} with alias. Hibernate keeps complaing that

"No column name found for property [time_of_last_update] [select {s.*}...."

So where did I set alias wrong?

Thanks,
AW

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

You don't need to treat h or q as an alias, only s. That's the only one that hibernate has to deal with, all the others are used only by the DBMS.

alwang · **Joined:** Sat Jun 17, 2006 9:04 pm **Posts:** 4

Wow, that works great! Thanks!

Now I am stuck again -- I am trying to get an aggregate result, say a total count like below,

select count(*) from (select s.* from site s left join health h on s.id = h.site
where h.status = ? and h.time_of_last_update =
(select max(q.time_of_last_update) from health q
where q.site = s.id) ) p

Again, this runs fine in MySQL console. But I am having trouble to have hibernate accept it, with or without alias. Any hints?

Thanks.

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

First tip: count(*) is (depending on your DBMS) frequently slower than count(your_pk_column), so your query will probably be better as

Code:

select count(s.id) as c
from site s
left join health h on s.id = h.site
where h.status = ?
and h.time_of_last_update =
  (select max(q.time_of_last_update) from health q where q.site = s.id)

If you want to do that in an SQLQuery, use addScalar("c", Hibernate.INTEGER) where you would have used addEntity in your previous query.

If you want to do that in HQL, the syntax is something like:

Code:

select count(s.id)
from site s
left join s.Health h
where h.Status = :status
  and h.lastUpdateTime = (select max(h1.lastUpdateTime) from Health h
                        where h.Site = s)

If you want to do it using criteria, it's along these lines:

Code:

Criteria crit = session.createCriteria(Site.class, "s");
crit.setProjection(Projections.rowCount());
DetachedCriteria subq = DetachedCriteria.forClass(Health.class, "h");
subq.createAlias("h.Site", "s1");
subq.setProjection(Projections.max("lastUpdateTime"));
subq.add(Property.forName("s1.id").eqProperty("s.id"));
crit.add(Property.forName("lastUpdateTime").eq(subq));
crit.list();

Both the HQL and Criteria versions assume that you have bidriectional associations or collections between Health and Site mappings.

Can you not just do "select count(s.id) from Site s join Health h on s.id = h.site" for this? An inner join should get what you want, seeing as any Site that has one or more equivalent Healths must have an Health with a highest time_of_last_update.

Don't forget to rate the previous post, if it helped.