How to check if the given input value is null or not in RoomDB-S

ghz 8months ago ⋅ 61 views
  • I'm writing an SQL query that returns data based on several conditions. One of these conditions is related to the senders field. If the senders value, which is an input parameter from the user, is null, I want to skip this specific condition and continue with the rest. However, the condition should be applied if senders is not null and contains a value.

P.S.: I'm using Room and Kotlin.

TLDR:

if(senders.isEmpty()){
    skipToNextConditions()
}else{
    applyTheCondition()
}
  • This is how the current Query looks like:
@Query(
    "SELECT * FROM localMail " +
    "WHERE (CASE WHEN :senders IS NOT NULL THEN `from` IN (:senders) END)" +
    "AND (isStarred = :inStarred OR isInTrash = :inTrash OR isInInbox = :inInbox OR hasAttachments = :hasAttachments OR isArchived = :inArchive) " +
    "AND (TRIM(:query) <> '' AND TRIM(:query) IS NOT NULL) " +
    "AND (rawMail COLLATE NOCASE LIKE '%' || :query || '%' OR subject COLLATE NOCASE LIKE '%' || :query || '%' OR intro COLLATE NOCASE LIKE '%' || :query || '%')"
)
fun queryAllSessionMails(
    senders: List<From>?,
    query: String,
    hasAttachments: Boolean,
    inInbox: Boolean,
    inStarred: Boolean,
    inArchive: Boolean,
    inTrash: Boolean,
): Flow<List<LocalMail>>
  • This query throws a runtime error which says:
android.database.sqlite.SQLiteException: near "IS": syntax error (code 1 SQLITE_ERROR): , while compiling: SELECT * FROM localMail WHERE  (CASE WHEN  IS NOT NULL THEN `from` IN () END) AND (isStarred=? OR isInTrash = ? OR isInInbox = ? OR hasAttachments = ? OR isArchived = ?) AND accountId = (SELECT accountId FROM localMailAccount WHERE isACurrentSession = 1 LIMIT 1) AND (TRIM(?) <> '' AND TRIM(?) IS NOT NULL)AND (rawMail COLLATE NOCASE LIKE '%' || ? || '%' OR subject COLLATE NOCASE LIKE '%' || ? || '%' OR intro COLLATE NOCASE LIKE '%' || ? || '%')
  • The exception does say there is a syntax error around IS, but I couldn't figure out where it's pointing to; I've gone through all of the related questions around this issue, and all I could find is using CASE (which I've already done in the above query) or checking if it's null or not, which I've already tested with the query mentioned above by modifying it like so:
"SELECT * FROM localMail " +
"WHERE (:senders IS NULL OR `from` IN (:senders))" +
"AND (isStarred = :inStarred OR isInTrash = :inTrash OR isInInbox = :inInbox OR hasAttachments = :hasAttachments OR isArchived = :inArchive) " +
"AND (TRIM(:query) <> '' AND TRIM(:query) IS NOT NULL) " +
"AND (rawMail COLLATE NOCASE LIKE '%' || :query || '%' OR subject COLLATE NOCASE LIKE '%' || :query || '%' OR intro COLLATE NOCASE LIKE '%' || :query || '%')"

Both of these changes result in the same runtime error. How can I resolve this issue?

Update

I've updated the query as follows:

@Query(
    "SELECT * FROM localMail WHERE " +
    "`from` IN (:senders)" +
    " AND (isStarred=:inStarred OR isInTrash = :inTrash OR isInInbox = :inInbox OR hasAttachments = :hasAttachments OR isArchived = :inArchive)" +
    " AND accountId = (SELECT accountId FROM localMailAccount WHERE isACurrentSession = 1 LIMIT 1)" +
    " AND (TRIM(:query) <> '' AND TRIM(:query) IS NOT NULL)" +
    "AND (rawMail COLLATE NOCASE LIKE '%' || :query || '%' OR subject COLLATE NOCASE LIKE '%' || :query || '%' OR intro COLLATE NOCASE LIKE '%' || :query || '%')"
)
fun queryCurrentSessionMails(
    senders: List<From>,
    query: String,
    hasAttachments: Boolean,
    inInbox: Boolean,
    inStarred: Boolean,
    inArchive: Boolean,
    inTrash: Boolean
): Flow<List<LocalMail>>
  • Although this doesn't throw any error, the data gets returned only if senders isn't empty. If it's empty, it returns nothing. However, I want to skip this condition and proceed with other conditions if senders is empty. If it's not empty, then apply it.
  • I've attached the respective images below:
  1. if senders isn't empty, it applies the filter and returns the value:

1img

  1. If senders is empty, it returns nothing when it should return based on other conditions:

2img

Answers

To conditionally apply the filter based on the senders parameter being empty or not, you can dynamically construct your query in the DAO method based on the value of senders. Here's how you can modify your DAO method to achieve this:

@Query(
    "SELECT * FROM localMail " +
    "WHERE (:senders IS NULL OR `from` IN (:senders)) " + // Conditionally apply the filter based on the value of senders
    "AND (isStarred = :inStarred OR isInTrash = :inTrash OR isInInbox = :inInbox OR hasAttachments = :hasAttachments OR isArchived = :inArchive) " +
    "AND accountId = (SELECT accountId FROM localMailAccount WHERE isACurrentSession = 1 LIMIT 1) " +
    "AND (TRIM(:query) <> '' AND TRIM(:query) IS NOT NULL) " +
    "AND (rawMail COLLATE NOCASE LIKE '%' || :query || '%' OR subject COLLATE NOCASE LIKE '%' || :query || '%' OR intro COLLATE NOCASE LIKE '%' || :query || '%')"
)
fun queryAllSessionMails(
    senders: List<From>?, // Nullable parameter
    query: String,
    hasAttachments: Boolean,
    inInbox: Boolean,
    inStarred: Boolean,
    inArchive: Boolean,
    inTrash: Boolean
): Flow<List<LocalMail>>

In the above query:

  • We're using the IS NULL check to determine if the senders parameter is null. If it is null, the condition (:senders IS NULL) will be true, and the from IN (:senders) condition will be skipped, effectively ignoring the filter based on senders.
  • If senders is not null, then the condition (:senders IS NULL) will be false, and the from IN (:senders) condition will be applied to filter based on the provided values in senders.

This way, you achieve conditional filtering based on the value of the senders parameter. If senders is null, the filter on from column will be skipped, and if it's not null, the filter will be applied based on the provided values.

#sql