query optimization - unnecessary join

Yrr86 · **Joined:** Sat Jun 06, 2009 7:55 am **Posts:** 3

Hi,
I'm currently trying to write a query with a simple subselect on a many-to-many join table in HQL.

I have two entity classes: User and Widget.
- Widget has a many-to-one association to User called 'owner'.
- User has a many-to-many association to itself called 'friends'.

Now I want to select all Widget entities for all friends of a single user (User entity provided as parameter).

In SQL, I could do that by quering the join table directly:

Code:

SELECT id, ... FROM widgets WHERE ownerId IN (SELECT friendId FROM users_friends WHERE userId = ?)

In HQL, I managed a similar behavior using the following code:

Code:

SELECT widget
FROM User user, Widget widget
WHERE user = ?
    AND widget.owner IN elements(user.friends)

However, this causes an unnecessary join into the User table.
Resulting SQL:

Code:

        select
            widget1_.id as id0_,
            widget1_.ownerId as ownerId0_
        from
            users user0_,        << unnecessary
            widgets widget1_ 
        where
            user0_.id=? 
            and (
                widget1_.ownerId in (
                    select
                        friends2_.friendId 
                    from
                        users_friends friends2_ 
                    where
                       user0_.id=friends2_.userId
                )
            )
       limit ?

Is there any way to make Hibernate use the user id directly inside the subselect?

Thanks,
Marc

geezenslaw · **Joined:** Wed Jun 03, 2009 10:33 am **Posts:** 4

Many-to-Many relations should be divided with the introduction of an intervening table such that the many-to-many relationship is maintained via a: many-to-one <------> one-to-many relationship. Try to introduce a middle table and see if your query issues improve. HTH, David.

Yrr86 · **Joined:** Sat Jun 06, 2009 7:55 am **Posts:** 3

Thanks for you reply - I will try that.