CODEWITHSUNDEEP
androidandroid-sqliteandroid-room
gmetax
gmetax

Reputation: 3973

Android, ROOM query with embedded list of objects

I am using ROOM and I am trying to get a vehicle name that has a specific Card.Code.

the entity classes are like that

@Entity(tableName = "vehicles")
data class Vehicle(
    @PrimaryKey
    @ColumnInfo(name = "id") val id: Int,
    @ColumnInfo(name = "name") val name: String,
    @ColumnInfo(name = "cards") val cards: List<Card>?
)

@Entity
data class Card(
    @ColumnInfo(name = "id") val id: Int,
    @ColumnInfo(name = "code") val code: String
)

my issue is that I don't know how to write correctly the SQL query

Upvotes: 6

Views: 4061

Answers (2)

dglozano
dglozano

Reputation: 6607

I would model the One-to-Many Relationship between Vehicle and Card using a FOREIGN KEY CONSTRAINT. Then, whenever you want to get the Vehicle name that has a specific Card you can make a query that joins both tables and selects the name column on those rows in which cardCode String is equal to the one given in the argument.

@Entity(tableName = "vehicles")
data class Vehicle(
    @PrimaryKey
    @ColumnInfo(name = "id") val id: Int,
    @ColumnInfo(name = "name") val name: String
)

@Entity(foreignKeys = arrayOf(ForeignKey(
            entity = Vehicle::class,
            parentColumns = arrayOf("id"),
            childColumns = arrayOf("vehicle_id"))
       )
)
data class Card(
    @PrimaryKey
    @ColumnInfo(name = "id") val cardId: Int,
    @ColumnInfo(name = "code") val code: String,
    @ColumnInfo(name = "vehicle_id") val vehicleId: Int,
)

@Query("SELECT vehicles.name
 FROM vehicles, card 
 WHERE vehicles.id == card.vehicle_id
 AND card.code == :cardCode")
fun getVehicleNameOfCard(cardCode: String): String?

Since Room is not an ORM, but just a SQLite wrapper, it doesn't handle embedded lists of objects for you. You can either flatten the list of Cards to a String using a TypeConverter (like I think you are doing now) or you can also opt to use the @Relation annotation.

For more information in how to deal with Relationships in Room, you can check this documentation.

Upvotes: 3

gmetax
gmetax

Reputation: 3973

my solution is like that

@Query("SELECT name FROM vehicles WHERE cards LIKE '%' || :cardCode || '%'")
fun getVehicleNameOfCard(cardCode: String): String?

but I don't like it and I prefer something better

Upvotes: 2

Related Questions