Room Android, select on two tables

I am new to Room and I’m struggling with it. I would like to do a simple select over two tables named Project and Tasks. These are the two classes and a third to link them :

    @Entity
    public class Project {
        @PrimaryKey(autoGenerate = true)
        private final long id;
        @NonNull
        private final String name;
        @ColorInt
        private final Integer color;
       }
    
    @Entity(foreignKeys = @ForeignKey(entity = Project.class, parentColumns = "id", childColumns = "projectId"))
    public class Task {
    
        @PrimaryKey(autoGenerate = true)
        private long idTask;
    
        // clé commune
        private long projectId;
    
        @NonNull
        private String nameTask;
    
        public long creationTimestamp;
    // constructor, getters, setters
       }
    
    public class TaskWithProject {
        @Embedded public Project project;
        @Relation(parentColumn = "id", entityColumn = "projectId")
        public Task task;
    }

I have tried many things in my Dao class. Here are two of those :

    @Dao
    public interface TaskDao {
    (...)
    
    // Solution 1
    @Query("SELECT * FROM Task t JOIN Project p ON t.projectId = p.id")
        LiveData<List<TaskWithProject>> getTaskWithProject();
    
    // Solution 2 from https://developer.android.com/training/data-storage/room/relationships
    @Transaction
    @Query("SELECT * FROM Task")
    LiveData<List<TaskWithProject>> getTaskWithProject();

My database is populated with these datas (no problem in the database inspector) :

    projectDao.insertProject(new Project(0, "Projet Tartampion", 0xFFEADAD1));
                    projectDao.insertProject(new Project(0, "Projet Lucidia", 0xFFB4CDBA));
                    projectDao.insertProject(new Project(0, "Projet " + "Circus", 0xFFA3CED2));
                    taskDao.insertTask(new Task(0, 1, "Task 1 - Tartampion"));
                    taskDao.insertTask(new Task(0, 1, "Task 2 - Tartampion"));
                    taskDao.insertTask(new Task(0, 2, "Task 1 - Lucidia"));

I get the data with this function :

    public LiveData<List<TaskViewStateItem>> getAllTasks() {
            return Transformations.map(repository.getTaskWithProject(), tasks -> {
                List<TaskViewStateItem> liststateitems = new ArrayList<>();
                    for (TaskWithProject t : tasks) {
                        Log.i(TAG, "getAllTasks: "+ t.toString());
                        liststateitems.add(new TaskViewStateItem(t.task.getIdTask(), t.task.getNameTask(), t.project.getName(), t.project.getColor(), t.task.getCreationTimestamp()));
                    }
                }
                return liststateitems;
            });
        }

With the solution 1 above I have 3 entries as expected but with a duplicate one.

    I/Log ViewModel: getAllTasks: TaskWithProject{project=Project{id=1, name='Projet Tartampion', color=-1385775}, task=Task{idTask=2, projectId=1, nameTask='Task 2 - Tartampion', creationTimestamp=1634723235}}
    I/Log ViewModel: getAllTasks: TaskWithProject{project=Project{id=1, name='Projet Tartampion', color=-1385775}, task=Task{idTask=2, projectId=1, nameTask='Task 2 - Tartampion', creationTimestamp=1634723235}}
        getAllTasks: TaskWithProject{project=Project{id=2, name='Projet Lucidia', color=-4928070}, task=Task{idTask=3, projectId=2, nameTask='Task 1 - Lucidia', creationTimestamp=1634723235}}

The solution 2 does not compile

    error: Not sure how to convert a Cursor to this method's return type (com.cleanup.todoc.model.TaskWithProject).
        LiveData<List<TaskWithProject>> getTaskWithProject();
                                        ^

I have no idea what I did wrong. So thank you very much for any help you can give me

Answer

As your TaskWithProject class has the project embedded, then it expects, to get the columns for the project and then build the task(s) from that and hence when you use SELECT * FROM Task it doesn’t have the pertinent columns (with the join it does BUT may not work as expected).

  • really TaskWithProject is ProjectWithTask.

So either use SELECT * FROM project or use a POJO with the Task Embedded and the Project as the @Relation (with SELECT * FROM task).