Android Room: How to read from two tables at the same time - duplicate id column

Question

I'm new to Android Room. I want to read from a table and also read from a related table. The relationship is pretty common. One table defines instances. The other table defines types. Imagine an Animal table and an AnimalType table. Pretty much any time that the Animal table has to be read, the AnimalType table needs to be read as well. E.g., we want to shows the animals name (from the Animal table) and the monkey icon (from the AnimalType table).

Based on an example from the Android Room documentation, this is the data class to model it:

public class AnimalWithType {
    @Embedded
    private Animal animal;

    @Embedded
    private AnimalType type;
    ...

The DAO can query the data with a single query. Room should be smart enough to populate the two child classes.

@Dao
public interface ZooDao
    @Query("select * from animal join animal_type on (animal.id = animal_type.id)")
    List<AnimalWithType> getAllAnimals();

As always theory is pretty. Reality is broken. The result is the following error:

Error:(8, 8) error: Multiple fields have the same columnName: id. Field names: animal > id, animalType > id.

Apparently, Room is confused about getting only one "id" column in the query and which class it belongs. There are two potential solutions: 1) We could create a column name alias for building_type.id and tell Room about it. 2) We already know that the foreign key from the Animal table to the AnimalType table (perhaps animal_type_id) has the other id column.

The question is how do we communicate that to Room. Is Room even able to handle that case?

Tips on how to solve this problem are appreciated. I hope that Room doesn't require doing something awkward like giving every id column a unique name.

Answer 1

waqaslam pointed the way to the solution. He suggested to alias the column names. The additional step is to give the second table a prefix.

public class AnimalWithType {
@Embedded
private Animal animal;

@Embedded(prefix = "type_")
private AnimalType type;
...

The column aliases are long and messy. I hope that someone can find a cleaner way.

@Query("select animal.id, animal.name..., animal_type.id as type_id... from animal join animal_type on (animal.id = animal_type.id)")