Query error: Not sure how to convert a Cursor to this method's return type-CodePudding

I have 2 DataModel class :

first one :

@Entity(tableName = TABLE_TASK)
data class TaskEntity(
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "task_id")  val taskId: Int,
    @ColumnInfo(name = "task_title") val taskTitle: String?,
    @ColumnInfo(name = "task_desc") val taskDesc: String?,
    @ColumnInfo(name = "task_priority") val taskPriority: Int?,
    @ColumnInfo(name = "task_status") val taskStatus: String?,
    @ColumnInfo(name = "task_time") val taskTime: String?,
    @ColumnInfo(name = "task_notify") val taskNotify: String?,
    @ColumnInfo(name = "task_subtask") val taskSubtask: MutableList<SubtaskModel>?
)

and

data class SubtaskModel(
    val subTasktitle : String?,
   val subTaskStatus : Boolean
)

and also i have TypeConverter Class :

     class MyConverter {
    
        @TypeConverter
        fun listToJson(value: List< SubtaskModel >?): String = Gson().toJson(value)
    
        @TypeConverter
        fun jsonToList(value: String) = Gson().fromJson(value, Array<SubtaskModel>::class.java).toList()
}

when I added a new record in the Database I have :

 TaskEntity(taskId=0, taskTitle=dailytask, taskDesc=sample, taskPriority=1, taskStatus=Upcoming, taskTime=Tue Feb 01 20:46:09 GMT 02:00 2022, taskNotify=, taskSubtask=[SubtaskModel(subTasktitle=sample1, subTaskStatus=false), SubtaskModel(subTasktitle=sample2, subTaskStatus=false)])

I don't have a problem in adding Task with subtask but my issue is about editing a record

i added this query for fetching the subtask from specific task that i need to edit it:

@Query("SELECT task_subtask FROM $TABLE_TASK WHERE task_id LIKE :id")
fun getAllSubTasks(id: Int): MutableList<SubtaskModel>

but after adding this query I have got the error :

error: Not sure how to convert a Cursor to this method's return type (com.sample.thetodoapplication.db.SubtaskModel). public abstract java.util.List<com.sample.thetodoapplication.db.SubtaskModel> getAllSubTasks(int id); ^ ^

thank you

CodePudding user response：

If you don't want to create another table you can use type converter.

Here is the link

Official Docs

CodePudding user response：

You need to make another table for your subtask datamodel and make relation between this table and your task table

Storing datamodel object( list of subtask datamodel) as a single column in table is completely wrong,

Read about relations in databases and then room doc, Room can handel your desire query so simple as you need. Here is the link:

https://developer.android.com/training/data-storage/room/relationships

Notice: if you dont use relations you should use Convertor(convert subtaskdatamodel to json and store it), this solution make your code hard and for future you have always complex problem( for example for nested query) so i advise you use relations :)

Have good coding, Soheil

CodePudding user response：

The issue is that Lists/Arrays for columns are not really supported. Although the TypeConverter you have handles retrieving the a TaskEntity, as you have found, it doesn't handle retrieving just the column.

A way to include a List as a column and to have the flexibility to retrieve that column is to use an intermediate ListHolder. A class that has a single member/field/var/val that is the List/Array and thus the column can have a single object.

So with, for example :-

data class SubTaskModelListHolder(
    val subtaskModelList: MutableList<SubtaskModel>
)

The Type Converters as:-

class MyConverter {

    @TypeConverter
    fun fromSubstaskModelListHolder(value: SubTaskModelListHolder): String {
        return Gson().toJson(value)
    }
    @TypeConverter
    fun toSubstaskModelListHolder(json: String): SubTaskModelListHolder {
        return Gson().fromJson(json,SubTaskModelListHolder::class.java)
    }
}

And then TaskEntity as :-

@Entity(tableName = TABLE_TASK)
data class TaskEntity(
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "task_id")  val taskId: Int,
    @ColumnInfo(name = "task_title") val taskTitle: String?,
    @ColumnInfo(name = "task_desc") val taskDesc: String?,
    @ColumnInfo(name = "task_priority") val taskPriority: Int?,
    @ColumnInfo(name = "task_status") val taskStatus: String?,
    @ColumnInfo(name = "task_time") val taskTime: String?,
    @ColumnInfo(name = "task_notify") val taskNotify: String?,
    //@ColumnInfo(name = "task_subtask") val taskSubTask: MutableList<SubtaskModel>?,
    @ColumnInfo(name = "task_subtask") val subTaskModelListHolder: SubTaskModelListHolder
)

and then for example/demo

@Dao
interface AllDao {

    @Insert
    fun insert(taskEntity: TaskEntity): Long
    @Query("SELECT * FROM $TABLE_TASK WHERE task_id LIKE :id")
    fun getAllTasks(id: Int): List<TaskEntity>

    @Query("SELECT task_subtask FROM $TABLE_TASK WHERE task_id LIKE :id")
    fun getJustTheSubtaskListById(id: Int): SubTaskModelListHolder
}

Then to demonstrate :-

    db = TheDatabase.getInstance(this)
    dao = db.getAllDao()

    dao.insert(
        TaskEntity(
            1,"Titile1","Description1",5,"Status1","Time1","Notify1",
            SubTaskModelListHolder(
                mutableListOf(
                    SubtaskModel("SubtaskTitle1",false),
                    SubtaskModel("SubtaskTitle2",true),
                    SubtaskModel("SubtaskTitle3",false)
                )
            )
        )
    )
    for (t: TaskEntity in  dao.getAllTasks(1)) {
        Log.d("DBINFO","Task is ${t.taskTitle} etc. there are ${t.subTaskModelListHolder.subtaskModelList.size} subtasks, they are:-")
        for(st: SubtaskModel in t.subTaskModelListHolder.subtaskModelList) {
            logSubtaskModel(st)
        }
    }

    for(st: SubtaskModel in dao.getJustTheSubtaskListById(1).subtaskModelList) {
        logSubtaskModel(st,"")
    }

When run from a fresh install (only designed to run the once) the output to the log includes :-

D/DBINFO: Task is Titile1 etc. there are 3 subtasks, they are:-
D/DBINFO:   Substask is SubtaskTitle1 status is false
D/DBINFO:   Substask is SubtaskTitle2 status is true
D/DBINFO:   Substask is SubtaskTitle3 status is false



D/DBINFO: Substask is SubtaskTitle1 status is false
D/DBINFO: Substask is SubtaskTitle2 status is true
D/DBINFO: Substask is SubtaskTitle3 status is false