CODEWITHSUNDEEP
androidrelationshipandroid-room
waldemar
waldemar

Reputation: 695

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();
}

Upvotes: 19

Views: 7754

Answers (3)

dphans
dphans

Reputation: 1683

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

Upvotes: 6

User Rebo
User Rebo

Reputation: 4600

As last option you can write all your queries yourself and combine them in an abstract DAO class:

@Dao
public abstract class UserDao { 

    @Transaction
    @Query("SELECT * FROM `User`")
    abstract List<UserWithPets> getUsers();

    @Transaction
    @Query("SELECT * FROM `Pet` WHERE userId = :userId AND type = :type")
    abstract List<Pet> getPetsByUser(int userId, String type);

    public List<UserWithPets> getUsersWithDogs() {
        List<UserWithPets> users = getUsers();
        for (User user: users) {
            List<Pet> pets = getPetsByUser(user.id, "dog");
            user.pets = pets;
        }
        return users;
    }
}


public class UserWithPets {
   @Embedded
   public User user;
   
   @Ignore
   public List<Pet> pets;
   
   // Other stuff
   // @Relation(parentColumn = "id", entityColumn = "parentId")
   // public List<Child> children;
 }

Upvotes: 0

brocky34
brocky34

Reputation: 178

In your DAO, you can specify any query you'd like. So you can do something like this:

@Query("select * from pet_table where userId = :userId and type = :type")
List<Pet> getPetsByUserAndType(int userId, String type)

Or something like that, I'm not sure what your table name is. Does that make sense?

Upvotes: 0

Related Questions