JPQL check many-to-many relationship

Question

Just a quick question:

There's the entity (for example User) who is connected with the ManyToMany relationship to the same entity (for example this relation describes "friendship" and it is symmetric).

What is the fastest way in terms of execution time to check if User A is a "friend" of user B? The "dumb" way would be to fetch whole List and then check if user exists there but that's obviously the overhead.

I'm using JPA 2

Here's the sample code:

@Entity
@Table(name="users")
public class UserEntity {
    @ManyToMany(fetch = FetchType.LAZY)
    private List<UserEntity> friends;

    ....
}

Answer 1

If you don't want to retrieve the whole List, what about using a MEMBER OF? Something like this:

SELECT user FROM UserEntity user WHERE :friend MEMBER OF user.friends

That would give you all people who have B as friend. If you want to restrict the results to A only, add a condition in the WHERE clause.

Not sure it's the best way to achieve what you want though. The "dumb" approach doesn't look so dumb actually.