弹簧数据jpa和多个集合的条件
问题描述:
我想通过一个所有应该匹配的列表。弹簧数据jpa和多个集合的条件
@Query("select g.name as name FROM Gym g " +
"LEFT JOIN g.listOfEquipment listOfEquipment " +
"WHERE " +
"(((:listOfEquipment) is null) or listOfEquipment.id in (:listOfEquipment)) "+
"AND (((:listOfAmenity) is null) or listOfAmenity.id in (:listOfAmenity))")
Page<Map<String, Object>> listing(@Param("listOfEquipment") Set<Integer> listOfEquipment,@Param("listOfAmenity") Set<Integer> listOfAmenity)
上述查询OR
工作,但我需要AND
假如我是路过1,2,3
那么结果应该是所有gym
谁拥有设备1 and 2 and 3
同为舒适性也
我试图 Finding items with a set containing all elements of a given set with jpql
但它没有帮助荷兰国际集团我监守有多个过滤器
网址是
http://localhost:8080/filter?listOfEquipment=5,3&listOfAmenity=51,13&sort=name
答
该查询应该给你Gym.id
与假设列表不包含重复项的所有listOfEquipment
相匹配。
select g.id as name
FROM Gym g
LEFT JOIN g.listOfEquipment listOfEquipment
WHERE listOfEquipment.id in (:listOfEquipment))
GROUP BY g.id
HAVING count(g.id) = #{#listOfEquipment.size}
你可以用它在你的主查询为listOfAmenity
类似查询的子查询。
select g.name as name
FROM Gym g
LEFT JOIN g.listOfEquipment listOfEquipment
WHERE (((:listOfEquipment) is null)
OR g.id in (
select g.id as name
FROM Gym g
LEFT JOIN g.listOfEquipment listOfEquipment
WHERE listOfEquipment.id in (:listOfEquipment))
GROUP BY g.id
HAVING count(g.id) = #{#listOfEquipment.size}
))
AND (((:listOfAmenity) is null)
OR g.id in (
select g.id as name
FROM Gym g
LEFT JOIN g.listOfAmenity listOfAmenity
WHERE listOfAmenity.id in (:listOfAmenity))
GROUP BY g.id
HAVING count(g.id) = #{#listOfAmenity.size}
))
答
没有在最后的,条件 缺少的支架应该是如下
@Query("select g.name as name FROM Gym g " +
"LEFT JOIN g.listOfEquipment listOfEquipment " +
"WHERE " +
"(((:listOfEquipment) is null) or listOfEquipment.id in (:listOfEquipment)) "+
"AND (((:listOfAmenity) is null) or listOfAmenity.id in (:listOfAmenity))")
+0
,因为这不是我原来的查询。 但它不能解决我的问题。 ,因为我不想'或'条件我需要'和'条件 –
“不工作”。我想知道这意味着什么。也许通过查看调用的SQL,你会发现更多?此处没有人看到实体类可能会丢失什么 – DN1