QueryDSL4 JPA SubQuery with @ElementCollection

Viewed 565

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?

1 Answers

For anyone who is coming here to find how to generally join @ElementCollection/@Embeddable classes that are not entities in JPAQuery, here it is:

QVideo_LineTag lineTag = QVideo_LineTag.lineTag; // defined by mvn compile
List<Video> baseQuery = new JPAQuery<Video>(entityManager)
    .from(video)
    .join(video.tags, lineTag)
    .where(lineTag.tag.in("a", "b"))
    .groupBy(video.id) // exclude repetitions when multiple tags of same video match
    .fetch();
Related