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 ?

For anyone with the same general question as the OP, this answer may help: https://stackoverflow.com/a/62790955/1617737 — ban-geoengineering, Jul 08 '20 at 08:48

score 0 · Accepted Answer · answered Oct 13 '19 at 10:13

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.

I think in this case using DatabaseViews is useless because i have to create new class for each little changes in SQLite query even in WHERE clause. But in DAO i can use bind parameters which make the @Query more flexible for simple coding purpose. — Mohammad Reza Khahani, Oct 16 '19 at 07:02

Room nested query and subquery

1 Answers1