JPQL query that returns entities involving a many to many relationship

Question

I need a JPQL query that returns:

All Tweets that I posted
All Tweets that are posted by users whom I follow (this is the many to many part)

I tried something like:

SELECT t
FROM Tweet t
WHERE t.author.id = :userId
OR t.author.id IN (
    SELECT u.followedUsers
    FROM User u
    WHERE u.id = :userId
)

Only to find out that the subquery has wrong syntax. Also I cannot call the relation table User_User, like I would in SQL, because JPA doens't recognize it

User

@Entity
public class User {

    @Id
    @GeneratedValue
    private long id

    @ManyToMany(mappedBy = "followedUsers")
    private Set<User> followers;

    @ManyToMany
    private Set<User> followedUsers;

}

Tweet

@Entity
public class Tweet {

    @Id
    @GeneratedValue
    private long id;

    @ManyToOne
    private User author;

}

Suggest that you look at something more like `t.author MEMBER OF {collection field}` — Neil Stockton, Apr 08 '17 at 12:28

score 1 · Accepted Answer · answered Apr 12 '17 at 04:08

1

I have the habit of making JPQL queries more similar to SQL queries. So, my suggestion:

SELECT t FROM Tweet t
JOIN t.author a
WHERE a.id = :idUser
OR a.id IN (SELECT followedUser.id 
    FROM User u
    JOIN u.followedUsers followedUser
    WHERE u.id = :idUser)

answered Apr 12 '17 at 04:08

Dherik

17,757
11
115
164

I didn't think about joining those tables, because, you know, JPA mostly handles relations between entities. Your answer keeps the query very readable, which is nice. This approach also works. – Lex Bauer Apr 12 '17 at 14:17

score 0 · Answer 2 · edited Apr 12 '17 at 14:50

0

given this query

OR t.author.id IN (
            SELECT u.followedUsers
            FROM User u
            WHERE u.id = :userId
        )

you are trying to find an Integer (t.author.id) in a list of User objects ! I think this should work :

 SELECT t
    FROM Tweet t
    WHERE t.author.id = :userId
    OR t.author MEMBER OF (
        SELECT u.followedUsers
        FROM User u
        WHERE u.id = :userId
    )

Edit

... MEMBER OF (subquery)... isn't allowed, but merging the subquery into its parent resolves this.

SELECT DISTINCT(t)
FROM Tweet t, User u
WHERE t.author.id = :userId
OR (t.author MEMBER OF u.followedUsers AND u.id = :userId)

edited Apr 12 '17 at 14:50

Lex Bauer

15
3

answered Apr 08 '17 at 12:34

Ammar Akouri

526
9
17

True, I compared integers to users. It should've been either `t.author.id IN :collectionOfIntegers` or `t.author MEMBER OF :collectionOfUsers`. Both of these examples work by the way, but then I'd have to know who I am following and send another query beforehand. Your guess `... MEMBER OF (SELECT...` gives a [compilation error](https://snag.gy/ZkTiNy.jpg), but you're close. JPQL did not accept a subquery there, so I tried to merge it. `SELECT DISTINCT(t) FROM Tweet t, User u WHERE t.author.id = :userId OR (t.author MEMBER OF u.followedUsers AND u.id = :userId)` This did it for me! – Lex Bauer Apr 12 '17 at 13:58

JPQL query that returns entities involving a many to many relationship

2 Answers2