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