CODEWITHSUNDEEP
androidandroid-room
Thomas Fischer
Thomas Fischer

Reputation: 1514

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

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.

Upvotes: 16

Views: 12813

Answers (3)

kosev
kosev

Reputation: 2225

I solved that problem by adding prefix to column names so they are unique. If your case does not allow changing column names you should use some of the provided answers and @Embedded(prefix) annotation.

@Entity(tableName = "animal")
public class Animal {
    @PrimaryKey
    @ColumnInfo(name = "animal_id")
    public long id;
    ...
}

@Entity(tableName = "animal_type")
public class AnimalType {
    @PrimaryKey
    @ColumnInfo(name = "animal_type_id")
    public long id;
    ...
}

And your query will be:

SELECT * FROM animal JOIN animal_type ON animal_id = animal_type_id

Upvotes: 3

Thomas Fischer
Thomas Fischer

Reputation: 1514

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)")

Upvotes: 10

waqaslam
waqaslam

Reputation: 68177

Instead of using * to collect all the fields, you need to specifically provide column names because id is found in both tables hence causing the error. Change your query to something like:

select animal.id AS 'ID', animal.columnA, animal.columnB,
animal_type.columnA, animal_type.columnB from animal
join animal_type on (animal.id = animal_type.id)

You dont need to fetch IDs from both tables since they will be same, so one is enough. However, replace coulmnA, columnB, etc. with the valid column names you want to fetch.

Upvotes: 2

Related Questions