QueryDSL4 JPA 子查询与@ElementCollection

Question

我有一个 @Entity命名 Video ，它包含 @ElementCollection标签:

@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;
    }
}

我想用标签过滤视频，SQL 看起来像这样

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')
)

但我不知道如何使用 JPAQuery 实现:

JPAQuery<Video> baseQuery = new JPAQuery<Video>(entityManager)
            .from(video)
            .where(video.tags.any().tag.in("a", "b"))
            .fetch();

结果是正确的，但是SQL更复杂:

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')
   )

是否可以使用 QueryDSL 4 简化 SQL？

Answer 1

对于来这里寻找一般加入方式的任何人@ElementCollection/@Embeddable不是 JPAQuery 中的实体的类， 这里是:

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();