Getting a query intersection in JPA?

Question

I have: "image" 1:Many "imageToTag" Many:1 "tag"

I want to issue a query that will return all images that have at least tags [a, b, c]. It's not clear to me how one can model this in JPQL. I could build the query string dynamically but that's bad for performance and security reasons. Any ideas?

Answer 1

In PSEUDO CODE:

SELECT * FROM IMAGES 
  INNER JOIN IMAGETAGS A
    ON IMAGE.ID = A.IMAGEID AND A.TAGID = 'A'
  INNER JOIN IMAGETAGS B
    ON IMAGE.ID = B.IMAGEID AND B.TAGID = 'B'
  INNER JOIN IMAGETAGS C
    ON IMAGE.ID = C.IMAGEID AND C.TAGID = 'C'

Just guessing, but the equivalent in JPQL would be

Select img from Image img 
 JOIN img.tag ta 
 JOIN img.tag tb 
 JOIN img.tag tc 
  WHERE ta.description = 'A' and tb.description = 'B' and tc.description = 'C'

Answer 2

How about this is jpa query language

Select img from Image img where img.tag.description in ('A','B','C');

Answer 3

Hi, I'm also having this problem.

I tried using this the query provided by Noah

Select img from Image img
JOIN img.tag ta
JOIN img.tag tb
JOIN img.tag tc
WHERE ta.description = 'A' and tb.description = 'B' and tc.description = 'C'

but it gave an empty result.

any help would be great