CREATE VIEW equivalent in new Room persistence library

Question

I have a RecyclerView of Top 10 users in my app. I populate it with a cursor that reads from a SQL VIEW in sql that aggregates from different tables that are related by foreign keys. I'd like to re write the SQLite boiler plate to use the new Room library. However, I do not know how a SQL VIEW is to be implemented in Room. DAO seems to be only for entities(tables), or perhaps I'm just missing something.

Answer 1

You can also use POJOs that join multiple tables from a DAO. From the Room documentation:

@Dao
public interface MyDao {
   @Query("SELECT user.name AS userName, pet.name AS petName "
          + "FROM user, pet "
          + "WHERE user.id = pet.user_id")
   public LiveData<List<UserPet>> loadUserAndPetNames();

   // You can also define this class in a separate file, as long as 
   // you add the "public" access modifier.
   static class UserPet {
       public String userName;
       public String petName;
   }
}

This also works fine with the aggregation functions.

Answer 2

Starting with 2.1.0-alpha01 version of Room library, you can define @DatabaseViews that internally make use SQLite of VIEW.

From 2.1.0-alpha02 you can even use them for fields annotated with @Relation.

@DatabaseView("SELECT user.id, user.name, user.departmentId," +
        "department.name AS departmentName FROM user " +
        "INNER JOIN department ON user.departmentId = department.id")
data class UserDetail(
    val id: Long,
    val name: String?,
    val departmentId: Long,
    val departmentName: String?
)

Example extracted from https://developer.android.com/training/data-storage/room/creating-views