CODEWITHSUNDEEP
androidkotlinandroid-room
Tobias Uhmann
Tobias Uhmann

Reputation: 3037

Android Room: Resolve foreign key in query

Given the following data model (i.e. one customer can have many orders and one order can have many shipments), how do I get a list of all orders with their associated customer and shipments for a certain order date?

data model

In Kotlin, I'd like to retrieve a list of type List<OrderWithCustomerAndShipments> with OrderWithCustomerAndShipments being a POJO like this:

data class OrderWithCustomerAndShipments(
    val order: Order,
    val category: Category,
    val shipments: List<Shipment>
)

Approach

So far, I've got a method that returns a List<OrderWithShipments>.

Note: For brevity, I omit @TypeConverter , @ForeignKey, @ColumnInfo, etc.

@Dao
interface Dao {
    @Transaction
    @Query("SELECT * FROM orders WHERE orderDate = :date")
    fun getOrdersWithShipments(date: Date): LiveData<List<OrderWithShipments>>
}

data class OrderWithShipments(
    @Embedded
    val order: Order

    @Relation(parentColumn = "id", entityColumn = "orderId")
    val shipments = List<Shipment>
)

@Entity
data class Customer(
    @PrimaryKey val id: Int,
    val customerName: String
)

@Entity
data class Order(
    @PrimaryKey val id: Int,
    val customerId: Int,
    val orderDate: Date,
)

@Entity
data class Order(
    @PrimaryKey val id: Int,
    val orderId: Int,
    val shipmentDate: Date,
)

One would assume that it is easier to resolve an order's foreign key to the parent customer than to get all child shipments. However, my attempts to get the parent customer haven't been successful so far.

Upvotes: 0

Views: 167

Answers (1)

sergiy tykhonov
sergiy tykhonov

Reputation: 5103

Have you tried approach below? You could use several @Relation in Room

@Dao
interface Dao {
    @Transaction
    @Query("SELECT * FROM orders WHERE orderDate = :date")
    fun getOrdersWithCustomerAndShipments(date: Date): LiveData<List<OrderWithCustomerAndShipments>>
}

data class OrderWithCustomerAndShipments(
    @Embedded
    val order: Order

    @Relation(parentColumn = "customerId", entityColumn = "id")
    val customer: Customer

    @Relation(parentColumn = "id", entityColumn = "orderId")
    val shipments: List<Shipment>
)

Upvotes: 1

Related Questions