Let's say I have the following three entities:
class Thing {
User owner;
}
class UserFriend {
User user;
User friend;
}
class User {
// collection key is friend in UserFriend
Set<UserFriend> friendUsers;
}
where they of course have mapping information, getters/setters, ids and all that stuff.

Here is the query I need to construct:
Find all Things belonging to my friends

The current implementation is (where ME is a previously retrieved User):
Criteria criteria = session.createCriteria( Thing.class )
.createCriteria( "owner" )
.createCriteria( "friendUsers" )
.add( Restrictions.eq( "user", ME );

This is the pseudo-statement being generated:
select thing from Things thing inner join Users user on thing.owner = user inner join UserFriends userfriend on user = userfriend.friend where userfriend.user = ME

What I need to do is avoid the unnecessary join on Users, so what I'm looking for is:
select thing from Things thing inner join UserFriends userfriend on thing.owner = userfriend.friend where userfriend.user = ME

I've tried all sorts of different rewrites of this query and can't manage to get rid of that join. Shouldn't Hibernate optimize out that unused inner join, or am I missing something? I'd appreciate any advise you might have.

I've found a partial solution, but unfortunately it requires a subselect in the where clause, which MySQL doesn't optimize out, but insteads re-executes for every row in the from even though its results don't depend on the from row.

Here it is:
DetachedCriteria myFriends = DetachedCriteria.forClass( UserFriend.class )
.add( Restrictions.eq( "user", ME )
.setProjection( Projections.property( "friend" ) );

Criteria criteria = session.createCriteria( Thing.class )
.add( Subqueries.propertyIn( "owner", myFriends );

I'd apprecitate it if someone knew how to rewrite this to get the subquery into the from clause where it will only be evaluated by MySQL once.

I've found another solution, but it's not pretty:
String query =
"select {thing.*} from things thing " +
"inner join userfriends uf on uf.friendId = thing.owner " +
"where uf.userId = " + ME.id;
SQLQuery q = session.createSQLQuery( query );
q.addEntity( "thing", Thing.class );

This gives me the exact resulting SQL that I've been looking for all along (obviously), but it's quite a mess when you factor in all the other stuff that needs to be included.


Basically what I'm still looking for is a Criteria-based solution that accomplishes the thing <-> userfriend join without the intermediate join on users, and doesn't involve subqueries.

Can a developer comment on if there are any plans to remove unnecessary joins in future releases, or if there is a way to get this done with the current release?