CODEWITHSUNDEEP
androidjoinandroid-sqliteandroid-roomone-to-one
TheRealChx101
TheRealChx101

Reputation: 1544

Android Room: How to return rows from a joined table

I have the following tables:

category:
id integer primary key autoincrement;
name text not null;

transaction:
id integer primary key autoincrement;
amount real not null;
category_id integer references category(id);

And have the following entity classes

class Category {
    @PrimaryKey int cat_id;
    String name;
}

class Transaction {
   @PrimaryKey int tx_id;

   double amount;

   @ForeignKey(entity = Category.class, parentColumns = "category_id", childColumns = "cat_id")
   int category_id;

   @Embedded Category category;
}

When I run the following query, Transaction.category is always null

select t.* from transaction t JOIN category c ON t.category_id = c.cat_id

So far, most of the tutorials online don't show how to handle this situation. In fact, it also turns out room inserts the @Embedded fields.

How can I structure the entities to make sure a single query to retrieve transactions returns with related categories? The relationship is always one-to-one.

I'm using RxJava and do not want to do second queries. I would like to have a single query return everything because it's displayed in a ReycyclerView.

Thanks

Upvotes: 0

Views: 506

Answers (1)

TheRealChx101
TheRealChx101

Reputation: 1544

Ok, so I found the answer from here: https://medium.com/androiddevelopers/database-relations-with-room-544ab95e4542 and https://developer.android.com/training/data-storage/room/relationships

So basically, the solution is to use the @Relation annotation , which can only be used on POJOs only as described here:

Please note this is for 1:1 relationships.

A one-to-one relationship between two entities is a relationship where each instance of the parent entity corresponds to exactly one instance of the child entity, and vice-versa.

For example, consider a music streaming app where the user has a library of songs that they own. Each user has only one library, and each library corresponds to exactly one user. Therefore, there should be a one-to-one relationship between the User entity and the Library entity.

First, create a class for each of your two entities. One of the entities must include a variable that is a reference to the primary key of the other entity.

@Entity
public class User {
    @PrimaryKey public long userId;
    public String name;
    public int age;
}

@Entity
public class Library {
    @PrimaryKey public long libraryId;
    public long userOwnerId;
}

In order to query the list of users and corresponding libraries, you must first model the one-to-one relationship between the two entities. To do this, create a new data class where each instance holds an instance of the parent entity and the corresponding instance of the child entity. Add the @Relation annotation to the instance of the child entity, with parentColumn set to the name of the primary key column of the parent entity and entityColumn set to the name of the column of the child entity that references the parent entity's primary key.

public class UserAndLibrary {
    @Embedded public User user;
    @Relation(
         parentColumn = "userId",
         entityColumn = "userOwnerId"
    )
    public Library library;
}

Finally, add a method to the DAO class that returns all instances of the data class that pairs the parent entity and the child entity. This method requires Room to run two queries, so add the @Transaction annotation to this method to ensure that the whole operation is performed atomically.

@Transaction
@Query("SELECT * FROM User")
public List<UserAndLibrary> getUsersAndLibraries();

Upvotes: 1

Related Questions