Using Hibernate 2 and mysql 4.0.8

I'm going to use an example to show our problem.

Let's say we have a box class. The box class has a collection of balls (called balls). The relationship is one-to-many, and is mapped via a Balls_In_Box table:

(snippit from box config)

<bag name="balls" table="Balls_In_Box">
<key column="box_id"/>
<one-to-many class="Ball"/>
</bag>

So what would the query look like that would return all boxes that contain no balls? It seems to me like nothing works...we've tried:

Query q = s.createQuery("select bx from bx in class Box, ball in bx.balls.elements group by bx having count(ball) = 0");

list = q.list()

but this always returns empty.

We could do a simple "select from Box as bx where bx.balls.size = 0" if our mysql supported that...

Any ideas? :|

~Jordan

I think this works:




p.s. "ball in bx.balls.elements" is a very deprecated syntax!q

Thanks that did it...but we were attempting to use the syntax on your tips and tricks page - maybe you should update it? I guess now's a good time to ask if one still needs to get the count of something using the
.iterate(string) thing. What would be nice is if

Query q = session.createQuery("from class where class.id = :id and class.param = :param).setInteger("id", id).setString("param", param);

count = ((Integer) s.iterate("select count(*) " + q.getQueryString()).next()).intValue();

worked, however it doesn't (since the getQueryString() returns the string with the : stuff included), and thus forcing one to re-write the select statement using concatination :|