How to query an M:N relationship with JPA2?

Question

I have an an object (BlogPost) that contains an M:N collection of elements (Tags).

How to query for an object (BlogPost) where at least one it its Tags matches an element in a set of Tags (defined by the user) with JPA2 (Hibernate).

findBlogPostWithAtLeastOneMatchingTag(Collection<Tag> tags){ ???? }

My main problem is, that I actually need to compare two collections of tags: - the collection of tags of the BlogPost. - the collection I search for

I tried Select p from Post p where p.tags in(:tags) but it does not work, as my post entities have more than just one tag.

So what could I do instead?

My BlogPost entity looks like this. It has several Tags.

@Entity
public class BlogPost{

    /** The tags. */
    @ManyToMany()
    @NotNull
    private Set<Tag> tags;

    @NotBlank
    private String content;

    ...
}

The solution must not be JPQL, JPA-Criteria (not Hibernate-Criteria) would be fine too.

Are you familiar with Criteria API? You can check it out and see if it suits you. — Fritz, Jul 02 '12 at 16:17
Familiar... well I "tried" to use it, but didn't find a way to make it work, neither. — Pumuckline, Jul 02 '12 at 16:24

perissf · Accepted Answer · 2012-07-06T08:17:19.967

9

If you like JPA Criteria, this is the solution for you:

List<Integer> myTagsIds = new ArrayList<Integer> ();
myTagsIds.add(1);
myTagsIds.add(2);

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<BlogPost> cq = cb.createQuery(BlogPost.class);
Root<BlogPost> blogPost = cq.from(BlogPost.class);
SetJoin<BlogPost, Tag> tags = blogPost.join(BlogPost_.tags);
Predicate predicate = tags.get(Tag_.id).in(myTagsIds);
cq.distinct(true);
cq.where(predicate);
TypedQuery<BlogPost> tq = em.createQuery(cq);
return tq.getResultList();

This solution makes use of the canonical MetaModel classes BlogPost_ and Tag_ that should be generated by your JPA implementation.

edited Jul 06 '12 at 08:17

answered Jul 05 '12 at 16:59

perissf

15,979
14
80
117

I think `person.join` should be `blog.join`? – Ralph Jul 06 '12 at 08:13
It was blogPost.join indeed. Thanks for pointing it out. I had tested the query on classes with different names (but same relationship however)... – perissf Jul 06 '12 at 08:18
So this stuff with the _ suffixes is only available for EclipseLink? – Alkanshel Oct 19 '15 at 23:18

score 2 · Answer 2 · answered Jul 05 '12 at 14:55

Approach 1:

In SQL it could be something like:

SELECT p FROM Post p WHERE (p.tags INTERSECT :tags IS NOT EMPTY);

Then apply the @SqlResultSetMapping.

Approach 2:

You can use Criteria API and start as you did but make a loop over Collection<Tag> tags as:

* make a union of single query results from `Select p from Post p where p.tags in(:tags)`;
* take distinct over result of union.

Query will be server-side and you wouldn't have to do dirty work in Java.

It's worth noting that INTERSECT is not available in mysql! – Alkanshel Oct 19 '15 at 23:13 — Alkanshel, Oct 19 '15 at 23:13

score 1 · Answer 3 · answered Jul 02 '12 at 16:14

1

You could do something like

Select t from Post t where t.tag in (select p.tag from Post p where p.id=:id)

id is the id of the current post. Basically you are selecting post with tags that are in the tags of current post.

answered Jul 02 '12 at 16:14

Deniz

1,575
1
16
27

This would actually work, if my post only had one tag (In fact it's the same idea I already had myself). I'm looking for a solution for al list of tags in Post t. I updated my question to make it clearer. – Pumuckline Jul 03 '12 at 11:24

How to query an M:N relationship with JPA2?

3 Answers3

Approach 1:

Approach 2:

Linked