Android - How to fetch sub list based on WHERE conditions from Room DB?

userrQ6a 0
Viewed 35

I am building an Android application in which I would like to fetch the list of active devices under the project manager.

Trying to put it in different way for better understanding

  • Project Manager table has list of employees
  • Employee table has list of devices

Now, we need the list of Project Managers with list of employees with device status either with 1 or 0 based on UI selection.

Entities

@Entity(tableName = TABLE_PROJECT_MANAGER)
data class ProjectManager(
    @PrimaryKey
    val id: String,
    val firstName: String?,
    val middleName: String?,
    val lastName: String?,
    @TypeConverters(EmployeesConverter::class)
    var employees: List<Employee>
  )

@Parcelize
data class Employee(
    val id: String,
    val name: String?,
    @TypeConverters(DeviceListTypeConverter::class)
    val devices : List<Device>? = null
)

@Parcelize
data class Device(
    @ColumnInfo(name = "device_id")
    @SerializedName("id")
    val id: String,
    val manufacturer: String?,
    val model: String?,
    val status: Int,
) : Parcelable

Type Converters:

EmployeesConverter

class EmployeesConverter {
    private val moshi = Moshi.Builder().build()

    private val membersType = Types.newParameterizedType(List::class.java, Employee::class.java)

    private val membersAdapter = moshi.adapter<List<Employee>>(membersType)

    @TypeConverter
    fun stringToMembers(member: String?): List<Employee>? {
        return member?.let {
            membersAdapter.fromJson(member)
        }
    }

    @TypeConverter
    fun membersToString(members: List<Employee>?): String? {
        return members?.let {
            membersAdapter.toJson(members)
        }
    }
}

DeviceListTypeConverter

class DeviceListTypeConverter {

    private val moshi = Moshi.Builder().build()
    private val membersType = Types.newParameterizedType(List::class.java, Device::class.java)
    private val membersAdapter = moshi.adapter<List<Device>>(membersType)

    @TypeConverter
    fun stringToMembers(member: String?): List<Device>? {
        return member?.let {
            membersAdapter.fromJson(member)
        }
    }

    @TypeConverter
    fun membersToString(members: List<Device>?): String? {
        return members?.let {
              membersAdapter.toJson(members)
        }
    }

}

I am little confused on how to achieve this. Please help me out on this.

android-room android
1 Answers

With what you currently have, you will need a query that has a WHERE clause that will find the appropriate status within the employees column. This is dependant upon how the Type Converter converts the List and the List.

This could be along the lines of:-

@Query("SELECT * FROM $TABLE_PROJECT_MANAGER WHERE instr(employees,'status='||:status)")
fun findProjectManagerWithDevicesAccordingToDeviceStatus(status: String): List<ProjectManager>

  • NOTE the above will very likely not work as is, you will very likely have to change 'status='||:status according to how the TypeConverter converts the employee list and the device list into the single employees column.

  • You would call the function with "0" or "1" respectively.

    • Of course you could use Int for status (Room will convert it to an SQLite string anyway)

In short you are embedding a List with an embedded List into a single value and thus finding the anecdotal needle in that haystack is complicated.

If this were approached from a database perspective then you would have tables (@Entity annotated classes) for each of the List's and as the relationships are probably many-many then tables that map/reference/associate/relate.

So rather than just the ProjectManager table, you would have an Employee table and a Device table and then a table for the mapping of a ProjectManager to the Employee(s) and a table for mapping the Employee to the Device(s). In which case you would have columns with specific values that can be queried relatively efficiently rather than an inefficient search through a complex single relatively large value bloated by the inclusion of data needed solely for the conversion to/from the underlying objects.

MikeT0
Related