Return type for Android Room joins

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?

Dao

@Query("SELECT * FROM Foo")
List<FooAndBar> findAllFooAndBar();

Class FooAndBar

public class FooAndBar {
    @Embedded
    Foo foo;

    @Relation(parentColumn =  "Foo.bar_id", entityColumn = "Bar.id")
    List<Bar> bar;
    // If we are sure it returns only one entry
    // Bar bar;

    //Getter and setter...
}

This solution seems to work, but I'm not very proud of it. What do you think about it?

Edit: Another solution

Dao, I prefer to explicitly select but "*" will do the job :)

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

Class FooAndBar

public class FooAndBar {
    @Embedded
    Foo foo;

    @Embedded
    Bar bar;

    //Getter and setter...
}

edit: since Version 2.2.0-alpha01, room @Relation annotation can manage One-To-One relation

Another option is to just write a new POJO representing the resulting structure of your JOIN query (which even supports column renaming to avoid clashes):

@Dao
public interface FooBarDao {
   @Query("SELECT foo.field1 AS unique1, bar.field1 AS unique2 "
          + "FROM Foo INNER JOIN Bar ON Foo.bar = Bar.id")
   public List<FooBar> getFooBars();

   static class FooBar {
       public String unique1;
       public String unique2;
   }
}    

See: room/accessing-data.html#query-multiple-tables

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.

> Is it possible to force a return type like this?

You can try @Embedded annotations on foo and bar. That will tell Room to try to take the columns from your query and pour them into foo and bar instances. I have only tried this with entities, but the docs indicate that it should work with POJOs as well.

However, this may not work well if your two tables have columns with the same name.

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