Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 3.0

Imagine I have two mapped objects, X and Y, and two join tables A and B that are also represented as mapped objects.

A
aid (pk)
xid (mapped to X)
yid (mapped to Y)

B
bid (pk)
xid (mapped to X)
yid (mapped to Y)

The basic HQL query I want is:
from A where x.id=?

Simple enough. The part I'm having trouble with is that I'd also like to test for the existence of (x,y) in B. So, along with A, I'd like to return true/1 or false/0 where true/1 indicates that there is a matching record B with B.x=A.x and B.y=A.y.

I can limit the result set to one or the other in the WHERE clause using a subselect, but I'd like to have it as a computed value so I can return both types of objects, and even sort/paginate on the value.

One approach I tried was to do a subselect in the from clause, something like:
from A a, (select count(*) from B b where a.x=b.x and a.y=b.y)=0 ...
but that doesn't appear to be valid HQL.

I've also tried adding a mapping in A to B, but I'm not sure how to get a one-to-one relationship out of it (constraining both x and y, though neither are a primary key in A or B).

Thanks for any suggestions.

If you don't mind dealing with an array of Objects being returned from your query, this should work:The returned array will contain your A object in slot 0, and the count (as an Integer object) in slot 1.

Thanks for the response. That definitely gets me partway there. I'll give it a shot.

I was hoping to be able to sort/paginate based on the count() as well. For example, the results may be shown in a UI paginated 5 at a time, with the count() value as a possible sort key.

That syntax can be extended to include order by. Check out section 14.15, "Tips and Tricks", of the ref docs.