I have an @Entity named Video, it contains @ElementCollection tags:
@Entity
@Table(name = "videos")
public class Video {
@Id private String id;
@ElementCollection
@CollectionTable(name = "video_tags",
joinColumns = {@JoinColumn(name = "video_id")})
private List<LineTag> tags = new ArrayList<>();
@Embeddable
public static class LineTag {
@Column
private String tag;
}
}
I want to filter videos with tags, the SQL looks like this
select
v.*
from
videos v
where exists (
select
1
from
video_tags vt
where
vt.video_id= v.id
and
tags2_.tag in ('a', 'b')
)
But I don't know how to make it with JPAQuery:
JPAQuery<Video> baseQuery = new JPAQuery<Video>(entityManager)
.from(video)
.where(video.tags.any().tag.in("a", "b"))
.fetch();
The result is correct, but the SQL is more complex:
select
v.*
from
videos v
where
exists (
select 1
from videos v1
inner join
video_tags vt
on v1.id = vt.video_id
where
v1.id = v.id
and vt.tag in ('a', 'b')
)
Is it possible to simplify the SQL with QueryDSL 4?