The shopping list Dao query below is returning "0" for the list_total column even though the value in the database is "3048". What could be wrong with the code?

Dao

@Query("SELECT * FROM shopping_lists WHERE is_include_in_lists_total = 1")
suspend fun getAllIncludedInTotalShoppingLists(): List<ShoppingListEntity>

ShoppingListModel

@Parcelize
data class ShoppingList(
    val id: Long = 0L,
    val name: String = "",
    val numberOfItems: Long = 0L,
    val listTotal: String = "0",
    val isCustomSortEdited: Boolean = false,
    val sortOrder: String = "None",
    val isIncludeInListsTotal: Boolean = false,
) : Parcelable

ShoppingListEntity

@Entity(tableName = "shopping_lists")
data class ShoppingListEntity(
    @PrimaryKey(autoGenerate = true)
    var id: Long = 0L,
    @ColumnInfo(name = "name")
    val name: String,
    @ColumnInfo(name = "number_of_items")
    val numberOfItems: Long = 0L,
    @ColumnInfo(name = "list_total")
    val listTotal: String,
    @ColumnInfo(name = "is_custom_sort_edited")
    val isCustomSortEdited: Boolean = false,
    @ColumnInfo(name = "sort_order")
    val sortOrder: String,
    @ColumnInfo(name = "is_include_in_lists_total")
    val isIncludeInListsTotal: Boolean = false,
)

Repository

override suspend fun getAllIncludedInTotalShoppingLists(): List<ShoppingList> {
   return withContext(Dispatchers.IO) {
        val returnedList = shoppingListDao.getAllIncludedInTotalShoppingLists().map {
            shoppingListMapper.mapFromEntity(it)
        }

        return@withContext returnedList
    }
}

Mapper

class ShoppingListMapper : EntityMapper<ShoppingListEntity, ShoppingList> {
    override fun mapFromEntity(entity: ShoppingListEntity, includeId: Boolean): ShoppingList {
        return if(includeId) {
            ShoppingList(
                id = entity.id,
                name = entity.name,
                numberOfItems = entity.numberOfItems,
                listTotal = entity.listTotal,
                sortOrder = entity.sortOrder,
                isCustomSortEdited = entity.isCustomSortEdited,
                isIncludeInListsTotal = entity.isIncludeInListsTotal
            )
        } else ShoppingList(
            name = entity.name,
            numberOfItems = entity.numberOfItems,
            listTotal = entity.listTotal,
            sortOrder = entity.sortOrder,
            isCustomSortEdited = entity.isCustomSortEdited,
            isIncludeInListsTotal = entity.isIncludeInListsTotal
        )
    }
}

CodePudding user response：

Your query as it stands would only select values that are 1, 3048 is not equal to 1.

You could use

"SELECT * FROM shopping_lists WHERE is_include_in_lists_total"

• as WHERE expects a logical/boolean from the ensuing expression so for numerical values any non-zero value (zero being false) will be true (such as 3048).
• as per

See

2.3. WHERE clause filtering.

If a WHERE clause is specified, the WHERE expression is evaluated for each row in the input data as a boolean expression. Only rows for which the WHERE clause expression evaluates to true are included from the dataset before continuing. Rows are excluded from the result if the WHERE clause evaluates to either false or NULL.

• https://www.sqlite.org/lang_select.html#whereclause

and in regards to boolean expressions

To convert the results of an SQL expression to a boolean value, SQLite first casts the result to a NUMERIC value in the same way as a CAST expression. A numeric zero value (integer value 0 or real value 0.0) is considered to be false. A NULL value is still NULL. All other values are considered true.

For example, the values NULL, 0.0, 0, 'english' and '0' are all considered to be false. Values 1, 1.0, 0.1, -0.1 and '1english' are considered to be true.

• and https://www.sqlite.org/lang_expr.html#booleanexpr

• You could also use "SELECT * FROM shopping_lists WHERE is_include_in_lists_total > 0".

CodePudding user response：

You can try it like this.

@Query("SELECT * FROM SHOPPINGLISTENTITY WHERE is_include_in_lists_total LIKE 1")

I think the problem is you write the wrong table name shopping_lists try and use LIKE instead of = I hope your problem is solved using this