Room relations with conditions

Question

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

you have to create an interface with anotation @Dao.For more refer below tutorial, you can get more knowldege https://medium.com/mindorks/sqlite-made-easy-room-persistence-library-ecd1a5bb0a2c — seon, Feb 28 '18 at 01:49

dphans · Accepted Answer · 2021-03-03T07:37:00.517

5

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

edited Mar 03 '21 at 07:37

answered Jun 05 '18 at 11:08

dphans

1,543
19
20

2

Do you have idea to make easier the "PETS._ID AS PETS__ID, PETS._USER_ID AS PETS__USER_ID" part? What about 30 param? – vihkat Jul 24 '18 at 21:02
Current, sql query language doesn't support group colums like `TABLE.* AS TABLE_*`. So, in this case, you can reference `SimpleSQLiteQuery` in Dao object, mean you can custom sql query by string process before query into database. Hope this help you! – dphans Jul 25 '18 at 05:05
2

I tried without success `warning: The query returns some columns [PETS__ID, PETS__USER_ID] which are not use by UserPets. You can use @ColumnInfo annotation on the fields to specify the mapping. You can suppress this warning by annotating the method with @SuppressWarnings(RoomWarnings.CURSOR_MISMATCH). Columns returned by the query: _ID, PETS__ID, PETS__USER_ID. Fields in UserPets: _ID.` – Yu-Hsuan Jan 13 '19 at 08:20
@dphans Why is AGE not defined in the Pet entity in your example? is this still working? can you maybe give an example in java? trying for hours now but can't get it to work in java. i'm either getting "Entities and Pojos must have a usable public constructor. You can have an empty constructor or a constructor whose parameters match the fields (by name and type). - java.util.List" or the error in the comment above. – nulldroid Apr 04 '19 at 13:29
How do I map all columns of user and pet table? Do I need to map all columns of pet with `as` in query or there is other simplified approach available? If my both table has more columns and i need all of the columns of first and second table to be fetched, then my query will be long. I wonder, if there is a simplest thing to map both table withot using `as` with all columns of second table. – Patriotic Jun 23 '20 at 05:22
Does using `GROUP BY` on a table returns a list? Because `UserPets` has a list of `Pet`s. – Sourav Kannantha B Nov 26 '21 at 17:35

score 0 · Answer 2 · answered Feb 27 '18 at 20:52

0

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?

answered Feb 27 '18 at 20:52

brocky34

168
1
11

1

But I want to get user data too – waldemar Feb 28 '18 at 08:05
1

Then use an inner join: @Query("select * from pet_table p inner join user_table t on p.userId = t.userID where userId = :userId and type = :type") List getPetsByUserAndType(int userId, String type) Or else, just use a separate query to get the user info by userId and combine the two data sets in the java. – brocky34 Feb 28 '18 at 14:54

score 0 · Answer 3 · answered Dec 20 '20 at 00:48

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

Room relations with conditions

3 Answers3

Linked