I'm having a bit of a problem coming up with an HQL query, and wondered if someone could help me....

In my domain model, I have a Topic, User and Subscription.
A Topic has a set of Subscriptions against it, and each Subscription belongs to a User. A User can have many simultaneous Subscriptions and additionally has the concept of a default Subscription.

So, a Topic has "Set getSubscriptions()", a Subscription has "User getUser" and additionally a User has "Subscription getDefaultSubscription".

Setting up the mappings and doing simple creats / finds has been fine.

However, I want to enforce a business rule that a Topic can not be deleted if it contains a default Subscription for a User with more than one subscription.
That is, a Topic containing a Subscription for which the following holds true:



Using my domain model its easy: Ask the Topic for its subscriptions, and for each Subscription - get the User, see if its the default subscription and if so see how many subscriptions it has.

The problem is, with a large number of subscriptions / Users, this could be very very inefficient.

So Im trying to come up with an HQL query to solve it more efficiently. I guess I want some sort of count query to find out for a given Topic whether it matches my criteria above.

It sounds quite tricky. Further, Im using MySql v4.0.x so I cant use subqueries.....
I've gone down a few avenues, but keep arriving at solutions that require subqueries.

Does anyone have any pointers that might help?
Or does it look like loading everything in (my first solution above) is the only way?

Thanks,

Dave

MySQL 4.0 supports joins, and as I remember my DBA said me that it is possible to replace practically every query with use of subqueries by query with inner joins. So try to use INNER/LEFT JOIN and HAVING clause, don't forget to GROUP BY the columns from HAVING clause.