CODEWITHSUNDEEP
listjpasetjpql
fea17e86
fea17e86

Reputation: 729

JPA JPQL: select items when attribute of item (list/set) contains another item

public class Document extends Model {
... 
@ManyToMany
public Set<User> accessors;
...
}

I want to select all Documents which accessors contain a certain user. I have just minimal experiences with SQL and no experiences with JPQL. So how to do that?

thanks in advance

Upvotes: 26

Views: 52990

Answers (2)

rhlobo
rhlobo

Reputation: 1346

SELECT d FROM Document AS d WHERE :user MEMBER OF d.accessors

Should be what you need, and it is simpler than joining tables. Just dont forget to use the user as a parameter instead of using its id:

query.setParameter("user", user);

Upvotes: 67

JB Nizet
JB Nizet

Reputation: 692003

select distinct d from Document d inner join d.accessors a where a.id = :id

You should learn how SQL joins work, and then learn how to use joins in JPQL. That's essential. You'll find plenty of tutorials online. Google is your friend.

Upvotes: 25

Related Questions