How should I query ManyToMany property by contains(Expression<T>) using QueryDSL with jpa Entity

Viewed 80

Observed vs. expected behavior

@Entity
@Data
class TSite {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    Integer id;
    String name;
    @ManyToMany
    Set<TKeyword> keywords;
}
@Entity
@Data
class TKeyword{
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    Integer id;
    String keyword;
    @ManyToMany
    Set<TSite> sites;
}
public interface RepoSite extends JpaRepository<TSite, Integer>, QueryDslPredicateExecutor<TSite> {
}

I would like to get a list of TSite which have keyword contains 'tech', implement SQL like this:

-- in statement
select * from tsite where tsite.id in (
    select tsite_keywords.sites_id from tsite_keywords 
    where tsite_keywords.keywords_id in (
        select tkeyword.id from tkeyword where tkeyword.keyword  like '%tech%'
    )
)
-- or 
-- join statement
SELECT
    DISTINCT tsite.*
FROM
    tsite
JOIN tsite_keywords ON tsite.id = tsite_keywords.sites_id
JOIN tkeyword ON tkeyword.id = tsite_keywords.keywords_id
AND tkeyword.keyword LIKE '%tech%'

I use the jpa querydsl like this repoSite.findAll( QTSite.tSite.keywords.contains( JPAExpressions.selectFrom(QTKeyword.tKeyword).where(QTKeyword.tKeyword.keyword.contains("tech"))) ) but it throw exception like this

SELECT tsite0_.id AS id1_18_, tsite0_.name AS name2_18_
FROM tsite tsite0_
WHERE (
    SELECT tkeyword1_.id
    FROM tkeyword tkeyword1_
    WHERE tkeyword1_.keyword LIKE ? ESCAPE '!'
) IN (
    SELECT keywords2_.keywords_id
    FROM tsite_keywords keywords2_
    WHERE tsite0_.id = keywords2_.sites_id
)
java.sql.SQLException: Subquery returns more than 1 row

How should I make it works?

Environment

Querydsl version: querydsl-jpa 5.0.0

Database: H2 or MySQl

JDK: java 11

0 Answers
Related