Room error: The columns returned by the query does not have the fields

Question

I made a screen like the image.

As you can see, the Set information items are in the red box.

I want to store these data in Room DB.

The red box is composed of Workout Class, and the blue box (WorkoutSetInfo class) is included in it.

I just want to get a list of set information from Room.

But I wrote a query to get a list of set information, I got the following error:

error: The columns returned by the query does not have the fields [id,set,weight,reps] in com.example.lightweight.data.db.entity.WorkoutSetInfo even though they are annotated as non-null or primitive. Columns returned by the query: [sets]
    public abstract androidx.lifecycle.LiveData<java.util.List<com.example.lightweight.data.db.entity.WorkoutSetInfo>> getWorkoutSetInfoList()

There have been several similar questions to mine, but I still haven't been able to solve them. How can I get only the list of set information?

---

WorkoutSetInfo

data class WorkoutSetInfo(
    val id: String = UUID.randomUUID().toString(), // For comparison in DiffUtil.
    val set: Int,
    val weight: String = "",
    val reps: String = ""
)

Workout

@Entity
data class Workout(
    @PrimaryKey(autoGenerate = true)
    val id: Int,
    val title: String = "",
    val unit: String = "kg",
    val memo: String = "",
    var sets: List<WorkoutSetInfo> = emptyList()
)

DAO

@Dao
interface WorkoutDao {
    @Query("SELECT sets FROM Workout")
    fun getWorkoutSetInfoList() : LiveData<List<WorkoutSetInfo>>

    @Insert
    fun insert(workout: Workout)
}

Repository

class WorkoutRepository(private val workoutDao : WorkoutDao, title: String) {
    val workout = Workout(0, title)
    var setInfoList : ArrayList<WorkoutSetInfo> = arrayListOf()
    
    val _items: LiveData<List<WorkoutSetInfo>> = workoutDao.getWorkoutSetInfoList()

    fun add(item: WorkoutSetInfo) {
        setInfoList.add(item)
        workout.sets = setInfoList

        workoutDao.insert(workout)
    }
}

ViewModel

class DetailViewModel(application: Application, title: String) : ViewModel() {
    private val repository: WorkoutRepository

    private lateinit var _items: LiveData<List<WorkoutSetInfo>>
    val items = _items
    private val list: List<WorkoutSetInfo>
        get() = _items.value ?: emptyList()

    init {
        val workoutDao = DetailDatabase.getDatabase(application)!!.workoutDao()
        repository = WorkoutRepository(workoutDao, title)
        _items = repository._items
    }

    fun addDetail() {

        viewModelScope.launch(Dispatchers.IO){
            val item = WorkoutSetInfo(set = list.size+1)
            repository.add(item)
        }
    }
}

---

UPDATED

class Converter {
    @TypeConverter
    fun listToJson(value: List<WorkoutSetInfo>) : String {
        return Gson().toJson(value)
    }

    @TypeConverter
    fun jsonToList(value: String) : List<WorkoutSetInfo> {
        return Gson().fromJson(value, Array<WorkoutSetInfo>::class.java).toList()
    }
}

Answer 1

You are trying to save a list of complex object in a column. Room does not know how to save that.

var sets: List<WorkoutSetInfo> = emptyList() <-- this is complex

Remember room is just sqlite underneath and your entities represent tables. Sqlite recognizes basic types but storing a list of complex objects is not something it can do by default. You can explore TypeConverters as a solution if you want to save this object in the same table.

See below for a relational solution that utilizes separate tables:

What you are looking for here is a one-to-many relation. A single Workout can have one or more WorkoutSetInfo

In the world of relational databases, you can represent this by having a separate table for WorkoutSetInfo and use the id from id of the work out to create a relation.

@Entity
data class WorkoutSetInfo(
    val id: String = UUID.randomUUID().toString(), // For comparison in DiffUtil.
    val set: Int,
    val weight: String = "",
    val reps: String = "",
    val workoutId: String // <-- this is the id of the workout that this set is associated with
)

Once you have that you can either write a join query or use a data object to query as defined here

You can have something like this:

data class WorkoutWithSets(
    @Embedded val workout: Workout,
    @Relation(
        parentColumn = "id",
        entityColumn = "workoutId"
    )
    val sets: List<WorkoutSetInfo>
)

And use something like this to query it

    @Transaction
    @Query("SELECT * FROM Workout WHERE id = :workoutId")
    fun getWorkoutWithSets(workoutId: String) : LiveData<WorkoutWithSets>

This will give the workout for the given id along with all the sets that match that workout id in the set table.