Android Room JOIN query splits results in to lists of size 1

Question

I have 2 Android room tables books and persons. Every book has a foreign key called owner_id which assigns it to a person entity. I am now trying to get a map that gives me a list of Books to each Person.

For that I am using this Method inside my DAO class :

@Transaction
@Query("SELECT * FROM books JOIN persons ON persons.id = book.owner_id")
public abstract Map<Person, List<Book>> getMap();

this returns a Map like this:

instead of my ideal Result:

My Room classes look like this:

@Entity
public class Person {

    @NonNull
    @PrimaryKey
    public UUID id;
    public String name="";
    @Override
    public boolean equals(@Nullable Object obj) {
        if(obj instanceof  Person)
            return ((Person) obj).equals(id);
        return super.equals(obj);
    }

    @Override
    public int hashCode() {
        return id.hashCode();
    }
}

@Entity
public class Book {

    @NonNull
    @PrimaryKey
    public UUID id;
    public UUID owner_id;
    public String name="";
    @Override
    public boolean equals(@Nullable Object obj) {
        if(obj instanceof  Book)
            return ((Book) obj).equals(id);
        return super.equals(obj);
    }

    @Override
    public int hashCode() {
        return id.hashCode();
    }
}

@Database(entities = {Person.class,Book.class}, version = 1)
@TypeConverters({Converters.class})
public abstract class LibarayDB extends RoomDatabase {
    public abstract LibarayDBdao libarayDBdao();
}

How can i get the returned map to contain only the key Person X once with a list of Book A and Book B as a value?

Answer 1

You use a POJO that has the Person member embedded, i.e. annotated with @Embedded and a List related i.e. annotated with @Relation.

You then use an @Query, preceded by @Transaction, to SELECT the respective Persons (no need for the JOIN) returning a List<> of the POJO.

As an example :-

class PersonWithListOfRelatedBooks {
   @Embedded
   Person person;
   @Relation(
           entity = Book.class,
           parentColumn = "id",
           entityColumn = "owner_id"
   )
   List<Book> bookList;
}

and :-

@Transaction
@Query("SELECT * FROM person")
List<PersonWithListOfRelatedBooks> getPersonWithRelatedBooks();

Demonstration

Using code modified for convenience

• Longs for id's so no need for type converters
• .allowMainThreadQueries, so can run on the main thread

The Book class :-

@Entity(
        /* ADDED as a suggestion (enforces referential integrity) */
        /* NOTE will fail if child(Book) is added without a parent (Person) */
        foreignKeys = {
                @ForeignKey(
                        entity = Person.class,
                        parentColumns = {"id"},
                        childColumns = {"owner_id"},
                        /* Optional but helps to maintain referential integrity */
                        onDelete = ForeignKey.CASCADE,
                        onUpdate = ForeignKey.CASCADE
                )
        }
)
public class Book {

    @NonNull
    @PrimaryKey
    public Long id; /* CHANGED FOR convenience */
    @ColumnInfo
    public Long owner_id; /* CHANGED FOR convenience */
    public String name="";
    @Override
    public boolean equals(@Nullable Object obj) {
        if(obj instanceof  Book)
            return ((Book) obj).equals(id);
        return super.equals(obj);
    }

    @Override
    public int hashCode() {
        return id.hashCode();
    }
}

The Person class :-

@Entity
public class Person {

   @NonNull
   @PrimaryKey
   public Long id;  /* CHANGED FOR convenience */
   public String name="";
   @Override
   public boolean equals(@Nullable Object obj) {
      if(obj instanceof  Person)
         return ((Person) obj).equals(id);
      return super.equals(obj);
   }

   @Override
   public int hashCode() {
      return id.hashCode();
   }
}

The LibarayDao interface :-

@Dao
interface LibarayDBdao {
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    long insert(Person person);
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    long insert(Book book);
    @Transaction
    @Query("SELECT * FROM person")
    List<PersonWithListOfRelatedBooks> getPersonWithRelatedBooks();

}

The PersonWithListOfRelatedBooks class (as above)

The LibarayDB abstract class :-

@Database(entities = {Person.class,Book.class}, version = 1, exportSchema = false /* ADDED to suppress warning */)
//@TypeConverters({Converters.class}) /* Commented out for convenience */
public abstract class LibarayDB extends RoomDatabase {
    public abstract LibarayDBdao libarayDBdao();
    private static volatile LibarayDB instance;
    public static LibarayDB getInstance(Context context) {
        if (instance==null) {
            instance = Room.databaseBuilder(context,LibarayDB.class,"libaray.db")
                    .allowMainThreadQueries() /* for convenience and brevity of the demonstration */
                    .build();
        }
        return instance;
    }
}

Some activity code:-

public class MainActivity extends AppCompatActivity {

    LibarayDB db;
    LibarayDBdao dao;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        db = LibarayDB.getInstance(this);
        dao = db.libarayDBdao();

        Person personX = new Person();
        personX.name = "PersonX";
        long personXId = dao.insert(personX);
        Book book = new Book();
        book.name = "BookA";
        book.owner_id = personXId;
        dao.insert(book);

        book.name = "BookB";
        dao.insert(book);

        StringBuilder sb = new StringBuilder();
        for(PersonWithListOfRelatedBooks pwrb: dao.getPersonWithRelatedBooks()) {
            for (Book b: pwrb.bookList) {
                sb.append("\n\tBook is " + b.name);
            }
            Log.d("DBINFO","Person is " + pwrb.person.name + " and has " + pwrb.bookList.size() + " books:-" + sb);
        }
    }
}

RESULT (as output to the log) :-

D/DBINFO: Person is PersonX and has 2 books:-
        Book is BookA
        Book is BookB

The Database (via App Inspection) :-

and :-