26
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

fea17e86
  • 711
  • 1
  • 9
  • 18

2 Answers2

65
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);
rhlobo
  • 1,286
  • 9
  • 10
  • 1
    What would be the difference if we use `.. :user in elements(d.accessors)` instead of `... :user MEMBER OF(d.accessors)`? – Manuel Jul 09 '15 at 08:35
  • 3
    @Manuel: [What's the difference between the IN and MEMBER OF jpql operators](http://stackoverflow.com/questions/5915822/whats-the-difference-between-the-in-and-member-of-jpql-operators) – Jacob van Lingen Nov 09 '16 at 09:51
26
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.

JB Nizet
  • 678,734
  • 91
  • 1,224
  • 1,255
  • @user1071828 you are supposed to click the tick to indicate that it was the right answer ;-) – fommil Aug 13 '12 at 12:10
  • can you please check this question? http://stackoverflow.com/questions/12051559/jpa-join-fetch-query-with-many-to-many – Panos Aug 21 '12 at 10:22
  • 3
    Please, do NOT forget the distinct. I couldn't figure out why I was ending up with duplicate entities :') – Jelle den Burger Mar 02 '17 at 15:35