@Query(value = "SELECT DISTINCT * "
"FROM chores WHERE id in (SELECT a.id "
"FROM chores as a INNER JOIN chores_assign_users as b ON a.id=b.chore_id "
"WHERE (a.is_deleted=FALSE "
"AND a.family_id=:familyId "
"AND (:userId IS NULL OR (cast(b.user_id as VARCHAR) IN :userId)) "
"AND (:status IS NULL OR (a.status IN :status)) "
"AND (:title IS NULL OR a.title LIKE cast(:title as text)) "
"AND (:from IS NULL OR :to IS NULL OR cast(created_at as VARCHAR) >= :from AND cast(created_at as VARCHAR) <= :to)) "
"ORDER BY :sortByDeadline"
", CASE WHEN :sortByDeadline THEN a.deadline END DESC "
", CASE WHEN NOT :sortByDeadline THEN a.created_at END DESC)",
countQuery = "SELECT COUNT(DISTINCT a.id) FROM chores as a INNER JOIN chores_assign_users as b ON a.id=b.chore_id "
"WHERE a.is_deleted=FALSE "
"AND a.family_id=:familyId "
"AND (:userId IS NULL OR (cast(b.user_id as VARCHAR) IN (:userId))) "
"AND (:status IS NULL OR (a.status IN (:status))) "
"AND (:title IS NULL OR a.title LIKE cast(:title as text))"
"AND (:from IS NULL OR :to IS NULL OR cast(created_at as VARCHAR) >= :from AND cast(created_at as VARCHAR) <= :to)) ",
nativeQuery = true)
ArrayList<Chore> findAlLFilteredByUserAndStatusAndTitleSortedByCreatedAtOrDeadLine(@Param("familyId") int familyId,
@Param("userId") List<String> userId,
@Param("status") List<String> status,
@Param("title") String title,
@Param("sortByDeadline") boolean sortByDeadline,
@Param("from") String from,
@Param("to") String to,
Pageable pageable);
我在 jpa 存盤庫中有這個本機查詢。如您所見，我搜索具有user_id IN :userId. 只要userId只包含一個元素，這就可以正常作業。但是如果userId包含多個元素，就會拋出這個例外org.postgresql.util.PSQLException: ERROR: argument of AND must be type boolean, not type record。同樣的問題發生在"AND (:status IS NULL OR (a.status IN :status)) "
user_id 具有整數型別
status 有型別 varchar
我很難找到解決此問題的方法。你能幫我解決這個問題嗎？而且，通過將每個元素轉換為字串，就像我userId在這種情況下所做的那樣，將整數串列作為字串串列傳遞給查詢是否很好？這可能是問題，我該如何解決？感謝您的時間！
uj5u.com熱心網友回復：
org.postgresql.util.PSQLException: ERROR: argument of AND must be type boolean, not type record.同樣的問題發生在"AND (:status IS NULL OR (a.status IN :status)) "
我認為使用COALESCE(:status) IS NULL而不是:status IS NULL將解決上述問題。
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/336541.html