sqlite DAO房间内有多个非强制过滤器

vwoqyblh  于 2022-11-15  发布在  SQLite
关注(0)|答案(1)|浏览(134)

我有一个应用程序,您可以使用过滤器的月份,年份和类别。它们不是强制性的,你也可以使用所有3个过滤器,只需月份和年份或任何可能的。
我在我的房间里尝试了SQL查询,DAO首先是这样的:

SELECT * 
FROM SaveEntryEntity 
WHERE " +
   "CASE " +
       "WHEN (:month) IS NOT NULL THEN substr(date,4,2) = (:month) " +
       "WHEN (:year) IS NOT NULL AND (:month) IS NULL THEN substr(date,7,4) = (:year) " +
       "WHEN (:kategory) IS NOT NULL THEN kategory = (:kategory) " + 
       "ELSE substr(date,4,2) = (:month) AND substr(date,7,4) = (:year) AND kategory = (:kategory) " +
   "END " +
   "OR " +
   "CASE " +
       "WHEN (:month) IS NOT NULL THEN substr(date,4,2) = (:month) " +
       "ELSE NULL " +
   "END AND " +
   "CASE " +
       "WHEN (:year) IS NOT NULL THEN substr(date,7,4) = (:year) " +
       "ELSE NULL " +
   "END"
         List<SaveEntryEntity> getMyAttributes(String month, String year, String kategory);

这样,我就可以只过滤月份,或者只过滤年份,而不能过滤一年中的月份。
另一次尝试是这样的:

@Query("SELECT * FROM SaveEntryEntity WHERE " +
        "(:month IS NULL OR substr(date,4,2) = :month) AND (:year IS NULL OR substr(date,7,4) = :year) OR " +
        "(:month IS NOT NULL AND substr(date,4,2) = :month) OR " +
        "(:kategory IS NULL OR kategory = :kategory)")
List<SaveEntryEntity> getMyAttributes(String month, String year, String kategory);`

但在这里,我甚至不能过滤单个参数。通过该查询,我希望过滤出年份和月份。
事实上,我希望能够用这三种过滤器来过滤任何可能的方式。
有人知道我怎样才能做到我所描述的那样吗?
非常感谢

5f0d552i

5f0d552i1#

我自己解决了这个问题。我上次尝试的逻辑基本上是正确的,我混淆了OR和AND。有了这一点,任何事情都能奏效:

@Query("SELECT * FROM SaveEntryEntity WHERE " +
        "(substr(date,4,2) = (:month) OR (:month) IS NULL) AND (:year IS NULL OR substr(date,7,4) = :year) AND " +
        "(substr(date,4,2) = (:month) OR (:month) IS NULL) AND (:kategory IS NULL OR kategory = :kategory) AND " +
        "(:year IS NULL OR substr(date,7,4) = :year) AND (:kategory IS NULL OR kategory = :kategory) AND " +
        "(substr(date,4,2) = (:month) OR (:month) IS NULL) AND (:year IS NULL OR substr(date,7,4) = :year) AND (:kategory IS NULL OR kategory = :kategory) AND " +
        "(substr(date,4,2) = (:month) OR (:month) IS NULL) AND " +
        "(:year IS NULL OR substr(date,7,4) = :year) AND " +
        "(:kategory IS NULL OR kategory = :kategory)")
List<SaveEntryEntity> getMyAttributes(String month, String year, String kategory);

相关问题