androidandroid-roomandroid-cursor

"Cannot figure out how to read this field from a cursor" when trying to get List of entries inside a custom return type

I was trying to return multiple values from the table with such a query: `

@Query("SELECT *, AVG(engineCapacity) as avgCapacity FROM Car WHERE bodyType=:bodyType")
fun getByBodyType(bodyType: BodyType): ParameterQueryResult

The return type code:

data class ParameterQueryResult(
    val cars: List<Car>,
    val avgCapacity: Double
)

And got the error: "Cannot figure out how to read this field from a cursor. private final java.util.List<com.example.project2.db.Car> cars = null;"

The same happened when I replaced the return type with Pair<List, Double>.

Code of the Entity class:

@Entity
data class Car(
    @PrimaryKey(autoGenerate = true) val id: Int = 0,
    val brand: String,
    val bodyType: BodyType,
    val color: String,
    val engineCapacity: Double,
    val price: Double,
)

Did anyone run into the same problem? Is it possible to return such combination of fields without using Cursor directly? Thanks a lot in advance

Solution

  • This is initially because Room doesn't know how to handle the var Car ParameterQueryResult as the class isn't actually a know class. So it needs to know what columns make up the car val. You can either include specific vals for each of the required vals from the car table OR more simply you can embed the Car class using @Embedded annotation, so

    data class ParameterQueryResult(
    @Embedded
    val cars: Car,
    val avgCapacity: Double
)

    Then, as the avg function is an aggregate function it returns a single value for all the rows of a group.

    In the absence of a GROUP BY clause, then the entire selection is the group (i.e. a single group aka 1 row for all the underlying rows selected by the WHERE clause).

    As such you might as well use:-

    @Query("SELECT AVG(engineCapacity) as whatever_as_it_does_not_matter_being_sinlge_value FROM Car WHERE bodyType=:bodyType")
fun getByBodyType(bodyType: String): Double

    However, say, for example, you wanted the average engine capacity for each color; then you would need to return, for example, List

    and you would then use:-

    @Query("SELECT *, avg(engineCapacity) AS avgCapacity FROM Car WHERE bodyType=:bodyType GROUP BY color")
fun getByBodyType(bodyType: String): List<ParameterQueryResult>

    Demo

    Using the following (as per the above):-

    data class ParameterQueryResult(
    @Embedded
    val car: Car,
    val avgCapacity: Double
)
@Dao
interface TheDAOs {
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(car: Car): Long
    @Query("SELECT avg(engineCapacity) FROM car WHERE bodyType=:bodyType")
    fun getAECByBodyType(bodyType: BodyType): Double
    @Query("SELECT *,avg(engineCapacity) AS avgCapacity FROM car WHERE bodyType=:bodyType")
    fun getCarAndAECByBodyType(bodyType: BodyType): List<ParameterQueryResult>
    @Query("SELECT *, avg(engineCapacity) AS avgCapacity FROM Car WHERE bodyType=:bodyType GROUP BY color")
    fun getByBodyType(bodyType: BodyType): List<ParameterQueryResult>
}

    And then in an activity (for convenience and brevity run on the main thread) :-

    class MainActivity : AppCompatActivity() {
    lateinit var db: TheDatabase
    lateinit var dao: TheDAOs
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

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

        dao.insert(Car(999, "brand1", BodyType("sedan",), "Red", 1500.00, 21999.00))
        dao.insert(Car(9, "brand2", BodyType("suv", 200), "Green", 1600.00, 32500.00))
        dao.insert(Car(99, "brand3", BodyType("hatch"), "Black", 1200.00, 18700.00))
        dao.insert(Car(9999, "brand4", bodyType = BodyType("sedan"), "White", 1300.00, 19999.99))
        dao.insert(Car(88, "brand1", BodyType("sedan"), "Metallic Red", 1500.00, 22499.99))

        for (c in dao.getCarAndAECByBodyType(BodyType("sedan"))) {
            Log.d("DBINFO_Q1", "AEC=${c.avgCapacity} CarID is ${c.car.id} Brand is ${c.car.brand} Color is ${c.car.color} .... ")
        }
        Log.d("DBINFO_Q2","AEC is ${dao.getAECByBodyType(BodyType("sedan"))}")
        for (c in dao.getByBodyType(BodyType("sedan"))) {
            Log.d("DBINFO_Q3", "AEC=${c.avgCapacity} CarID is ${c.car.id} Brand is ${c.car.brand} Color is ${c.car.color} .... ")
        }

    }
}

    The result is :-

    2022-10-30 16:55:37.590 D/DBINFO_Q1: AEC=1433.3333333333333 CarID is 88 Brand is brand1 Color is Metallic Red .... 
2022-10-30 16:55:37.592 D/DBINFO_Q2: AEC is 1433.3333333333333
2022-10-30 16:55:37.595 D/DBINFO_Q3: AEC=1500.0 CarID is 88 Brand is brand1 Color is Metallic Red .... 
2022-10-30 16:55:37.595 D/DBINFO_Q3: AEC=1500.0 CarID is 999 Brand is brand1 Color is Red .... 
2022-10-30 16:55:37.596 D/DBINFO_Q3: AEC=1300.0 CarID is 9999 Brand is brand4 Color is White ....