Return type for Android Room joins

Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.
Learn more about Collectives
Teams
Q&A for work
Connect and share knowledge within a single location that is structured and easy to search.
Learn more about Teams
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?
    @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 :)
Keep in mind that this solution only works when the fields of both entities are unique. See the comments for more information.
@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
                If there are conflicts between Foo and Bar, I believe you can eliminate these by creating a subset representation of the latter class, e.g. public class BareBar { ...some bar fields... }, and then adding entity = BareBar.class to the @Relation. See: developer.android.com/reference/android/arch/persistence/room/…
– charles-allen
                Dec 28, 2017 at 19:58
                Second solution causes "Multiple fields have the same columnName" compile error then entities have PK property named the same: id
– Vinigas
                Jun 21, 2018 at 7:23
                Does the second solution actually work? Coz I am getting "Cannot figure out how to convert Cursor..." error. Additionally I am using @Embedded(prefix = "foo_") & @Embedded(prefix = "bar_")
– musooff
                Mar 6, 2019 at 8:48
                @musooff The second solution only works for me when there is no prefix value set on the @Embeddeds. To workaround duplicate column names I had to use @ColumnInfo(name = ...) for each class field on each table.
– N1hk
                Sep 30, 2019 at 2:35
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):
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:
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.
                This query is quite may result in freezing the app when data in both tables crosses 1000. Will you suggest me how to avoid freezing the app when query grows and return result grows @dphans
– Suresh Maidaragi
                Aug 23, 2019 at 10:16
                @SureshMaidaragi Use with paging library. Now change return query from LiveData<List<Attribute>> into DataSource.Factory<Int, Attribute>. Otherway, use LIMIT page size from query.
– dphans
                Sep 10, 2019 at 7:25
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.
                @pqvst: "how should I cleanly construct a compatible return type that includes all fields for both entities?" -- either pick foo or bar to be @Embedded and put the remaining fields directly in FooAndBar, or put all fields directly in FooAndBar. Use AS in the SQL to rename duplicate columns in the result set, and use @ColumnInfo as needed to map those AS-renamed columns into what you want the fields to be.
– CommonsWare
                Jul 12, 2017 at 15:11
                @pqvst: As it stands, your query should result in either a SQLite error about duplicate columns in the output, or at best the Cursor that SQLiteDatabase creates under the covers will not have both id columns (and any other duplicated columns). You need all the columns in a SQL query to have distinct names in the result set, otherwise the Cursor will not have all the data. Once you fix that, adjust the Foo and Bar entities to match and use the @Embedded solution.
– CommonsWare
                Jul 12, 2017 at 15:24
                @pqvst: "Doesn't feel very "clean" to me" -- then get rid of the JOIN and make two DAO calls, one to get the Foo, the other to get the associated Bar. The express intention with Room is that arbitrary queries result in arbitrary POJOs for the output, much as how you need appropriate POJOs for Retrofit calls. Entities are there mostly for plain CRUD.
– CommonsWare
                Jul 12, 2017 at 15:26
@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
        Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
				I cannot figure out how to include a WHERE clause in an Android Room @Query annotation query where multiple table queries are necessary
                See more linked questions