Return type for Android Room joins

Question

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?

Answer 1

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

Answer 2

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