Return type for Android Room joins

user_966782 0
Viewed 52820

Let's say I want to do an INNER JOIN between two entities Foo and Bar:

@Query("SELECT * FROM Foo INNER JOIN Bar ON Foo.bar = Bar.id")
List<FooAndBar> findAllFooAndBar();

Is it possible to force a return type like this?

public class FooAndBar {
    Foo foo;
    Bar bar;
}

When I try to do that, I get this error:

error: Cannot figure out how to read this field from a cursor.

I've also tried aliasing the table names to match the field names, but that didn't work either.

If this isn't possible, how should I cleanly construct a compatible return type that includes all fields for both entities?

android-architecture-components android-room android
5 Answers

Try this way. For example, I have M2M (many-to-many) relations between Product and Attribute. Many Products have many Attributes and I need to get all Attributes by Product.id with sorted records by PRODUCTS_ATTRIBUTES.DISPLAY_ORDERING.

|--------------|  |--------------|  |-----------------------|
| PRODUCT      |  | ATTRIBUTE    |  | PRODUCTS_ATTRIBUTES   |
|--------------|  |--------------|  |-----------------------|
| _ID:  Long   |  | _ID: Long    |  | _ID: Long             |
| NAME: Text   |  | NAME: Text   |  | _PRODUCT_ID: Long     |
|______________|  |______________|  | _ATTRIBUTE_ID: Long   |
                                    | DISPLAY_ORDERING: Int |
                                    |_______________________|

So, models will be like below:

@Entity(
    tableName = "PRODUCTS",
    indices = [
        Index(value = arrayOf("NAME"), unique = true)
    ]
)
class Product {

    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "_ID")
    var _id: Long = 0

    @ColumnInfo(name = "NAME")
    @SerializedName(value = "NAME")
    var name: String = String()

}


@Entity(
    tableName = "ATTRIBUTES",
    indices = [
        Index(value = arrayOf("NAME"), unique = true)
    ]
)
class Attribute {

    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "_ID")
    var _id: Long = 0

    @ColumnInfo(name = "NAME")
    @SerializedName(value = "NAME")
    var name: String = String()

}

And the "join" table will be:

@Entity(
    tableName = "PRODUCTS_ATTRIBUTES",
    indices = [
        Index(value = ["_PRODUCT_ID", "_ATTRIBUTE_ID"])
    ],
    foreignKeys = [
        ForeignKey(entity = Product::class, parentColumns = ["_ID"], childColumns = ["_PRODUCT_ID"]),
        ForeignKey(entity = Attribute::class, parentColumns = ["_ID"], childColumns = ["_ATTRIBUTE_ID"])
    ]
)
class ProductAttribute {

    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "_ID")
    var _id: Long = 0

    @ColumnInfo(name = "_PRODUCT_ID")
    var _productId: Long = 0

    @ColumnInfo(name = "_ATTRIBUTE_ID")
    var _attributeId: Long = 0

    @ColumnInfo(name = "DISPLAY_ORDERING")
    var displayOrdering: Int = 0

}

In, AttributeDAO, to get all attributes based on Product._ID, you can do something like below:

@Dao
interface AttributeDAO {

    @Query("SELECT ATTRIBUTES.* FROM ATTRIBUTES INNER JOIN PRODUCTS_ATTRIBUTES ON PRODUCTS_ATTRIBUTES._ATTRIBUTE_ID = ATTRIBUTES._ID INNER JOIN PRODUCTS ON PRODUCTS._ID = PRODUCTS_ATTRIBUTES._PRODUCT_ID WHERE PRODUCTS._ID = :productId ORDER BY PRODUCTS_ATTRIBUTES.DISPLAY_ORDERING ASC")
    fun getAttributesByProductId(productId: Long): LiveData<List<Attribute>>

}

If you have any questions, please tell me.

user_36960790

This is my Food Table

@Entity(tableName = "_food_table")
data class Food(@PrimaryKey(autoGenerate = false)
            @ColumnInfo(name = "_food_id")
            var id: Int = 0,
            @ColumnInfo(name = "_name")
            var name: String? = "")

This is my Cart table and model class (Food Cart)

@Entity(tableName = "_client_cart_table")
data class CartItem(
                @PrimaryKey(autoGenerate = false)
                @ColumnInfo(name = "_food_id")
                var foodId: Int? = 0,
                @Embedded(prefix = "_food")
                var food: Food? = null,
                @ColumnInfo(name = "_branch_id")
                var branchId: Int = 0)

Note: Here we see _food_id column in two table. It will throw compile time error. From @Embedded doc, you have to use prefix to differentiate between them.

Inside dao

@Query("select * from _client_cart_table inner join _food_table on _client_cart_table._food_id = _food_table._food_id where _client_cart_table._branch_id = :branchId")
fun getCarts(branchId: Int) : LiveData<List<CartItem>>

This query will return data like this

CartItem(foodId=5, food=Food(id=5, name=Black Coffee), branchId=1)

I have done this in my project. So Give it a try. Happy coding

user_53315660
Related