Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Room relations with conditions

How to add conditions to the relation?

For example, we have object Pet

    @Entity
     public class Pet {
         @ PrimaryKey
         int id;
         int userId;
         String name;
         String type;
         // other fields
     }

and object User

public class User {
     int id;
     // other fields
 }

For getting user with pets we make object

public class UserAllPets {
   @Embedded
   public User user;
   @Relation(parentColumn = "id", entityColumn = "userId", entity = Pet.class)
   public List<PetNameAndId> pets;
 }

How is possible to get user with pets by type? Only dogs or only cats

Here is dao class:

@Dao
public abstract class UserDao { 

   @Query("SELECT * FROM `users`")
   public abstract UserAllPets getUserWithPets();
}
like image 445
waldemar Avatar asked Feb 27 '18 09:02

waldemar


People also ask

Is Room database relational?

Because SQLite is a relational database, you can define relationships between entities. Even though most object-relational mapping libraries allow entity objects to reference each other, Room explicitly forbids this.

What is foreign key room android?

android.arch.persistence.room.ForeignKey. Declares a foreign key on another Entity . Foreign keys allows you to specify constraints across Entities such that SQLite will ensure that the relationship is valid when you modify the database.

What is room DB Android?

Room Database is a part of the Android Architecture components which provides an abstraction layer over SQLite which allows for more robust database access while still providing the full power of SQLite. Room is a persistence library, part of the Android Jetpack. Android Developers.


1 Answers

Just create a wrapper from your owner model, using Embedded and query JOIN in your DAO object.

For example: User have many Pets. We will find all Pet, filter by User's id and Pet's age greater equal than 9:

@Entity(tableName = "USERS")
class User {
    var _ID: Long? = null
}

@Entity(tableName = "PETS")
class Pet {
    var _ID: Long? = null
    var _USER_ID: Long? = null
    var AGE: Int = 0
}

// Merged class extend from `User`
class UserPets : User {
    @Embedded(prefix = "PETS_")
    var pets: List<Pet> = emptyList()
}

And in your UserDao

@Dao
interface UserDao {
    @Query("""
         SELECT USERS.*, 
                PETS._ID AS PETS__ID, 
                PETS._USER_ID AS PETS__USER_ID 
         FROM USERS 
             JOIN PETS ON PETS._USER_ID = USERS._ID 
         WHERE PETS.AGE >= 9 GROUP BY USERS._ID
           """)
    fun getUserPets(): LiveData<List<UserPets>>
}

SQL Syntax highlighted:

SELECT USERS.*, 
       PETS._ID AS PETS__ID, 
       PETS._USER_ID AS PETS__USER_ID 
FROM USERS 
    JOIN PETS ON PETS._USER_ID = USERS._ID 
WHERE PETS.AGE >= 9 GROUP BY USERS._ID
like image 92
dphans Avatar answered Sep 28 '22 06:09

dphans