"Cannot figure out how to read this field from a cursor" when trying to get List of entrie-CodePudding

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

CodePudding user response：

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
)

@Embedded is a shortcut that includes all the individual vals from the embedded class.

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).

in which case avgCapacity will be the expected value BUT the values for Car will be arbritary values (and pretty much useless/unreliable).

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

note that the alias (AS whatever_as_it_does_not_matter_being_sinlge_value) does not matter as Room will just get the single value (index 0 from the underlying Cursor)
- you might as well have SELECT AVG(engineCapacity) FROM Car WHERE bodyType=:bodyType

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>

i.e. as you may have 1 or more groupings then now you want to extract as list BUT:-
as indicated by using arbritraryCar the Car's brand, bodytype, engine capacity and price will be the values from one of the rows that make up the group (again pretty much useless/unreliable)
- obviously color will be the same value per group
- again you might as well just return a List with the query just having the single output column i.e. avg(engineCapacity), although then you would perhaps have an issue determining what value is associated with what color. In which case you may to output a List based upon a class that is comprised of a String for the color and a double for the average.

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 ....

What relevance is the Car with an ID of 88 to the result? Why id 88 and not one of the other cars? Why Metallic Red and not Red or White (the other colors of the cars with a sedan body type)? ALL RHETORICAL
- 88 because SQLite has used what it considers the most appropriate index to ascertain the data (the primary key and the only index and 88 is the lowest value of the selected rows used to ascertain the avg)
The second output (DBINFO_Q2) is exactly the same average and there is no need for the additional POJO
The last three rows are because of the 3 colors of the cars that have the sedan body type i.e. grouped by color.
- of course if there were 2 Red sedans then id and price are arbitrary.