Room nested query and subquery

Question

I use Room in my Android project and want to write a complex query. I search about it and there is some answers witch says use @Embedded like this:

class TripAndListsAndListItems {
      @Embedded
      var trip: Trip? = null

     @Relation(parentColumn = "creatorId", entityColumn = "remoteId",     entity = User::class)
     var user: List<User>? = null

     @Relation(parentColumn = "remoteId", entityColumn = "tripId", entity = PlanitiList::class)
     var lists: List<ListAndListItems>? = null
}

Here is complete article.

But then i have to figure it out in my code to extract my result using loops and so on. I wrote my query in @Query with nested query and match columns with entity fields by using "as" like this:

Here is the ViewModel class:

class ServiceCard(
    val id: Int,
    val customerInfo: String,
    val time: String,
    val oilFilter: Boolean,
    val airFilter: Boolean,
    val gasFilter: Boolean,
    val oil: Boolean
)

and @Doa has a @Query method like this:

@Dao
interface ServiceCardDao :ICommonDao<ServiceCard>{

   @Query("SELECT s.services_id as id,  " +
        "s.user_mobile_no as customerInfo, " +
        "( " +
        "SELECT count(*) " +
        "FROM service_detail as sd " +
        "WHERE sd.services_id = s.services_id and sd.service_type_id = 1 " +
        ")              as oilFilter, " +
        "( " +
        "SELECT count(*) " +
        "FROM service_detail as sd " +
        "WHERE sd.services_id = s.services_id and sd.service_type_id = 2 " +
        ")         as airFilter, " +
        "( " +
        "SELECT count(*) " +
        "FROM service_detail as sd " +
        "WHERE sd.services_id = s.services_id and sd.service_type_id = 3 " +
        ")         as gasFilter,  " +
        "( " +
        "SELECT count(*) " +
        "FROM service_detail as sd " +
        "WHERE sd.services_id = s.services_id and sd.service_type_id = 4 " +
        ")         as oil, " +
        "s.service_date as time " +
        "FROM services as s ")
   fun selectAllServicesWithDetail(): LiveData<List<model.ServiceCard>>

}

Is there any advantage or disadvantage between these 2 ?

Answer 1

Both have advantages over the other.

The coding in the Dao is more complex for one, the other is more complex to code in the Entity but the greater complexity in the Dao is greater than the complexity difference of the Entities. So the simpler Dao may well win favour with some.

One would be more efficient as there is no post data retrieval looping to obtain the counts and additionally that SQLite is compiled C code rather than JVM bytecode which has to be interpreted so SQlite is typically very efficient. However, efficiency may well well be what some are willing to forsake for the sake of simpler coding and perhaps for what one is used to.

Some would perhaps consider alternatives such as DatabaseViews which combines Dao and Class as one.