Room Database distinct value

Question

I am trying to integrate the room database in my android application. Now I want to query distinct result from DB but I am getting this error:

error: The columns returned by the query does not have the fields [id] in com.abc.def.model.User even though they are annotated as non-null or primitive. 
    Columns returned by the query: [user_name]

My Entity (Getter and Setter are there not copying here):

@Entity
public class User {
     @PrimaryKey(autoGenerate = true)
        @NonNull
        @ColumnInfo(name = "id")
        private Integer id;
    
        @ColumnInfo(name = "user_name")
        @NonNull
        private String name;
    
        @ColumnInfo(name = "email")
        private String email;
    
        public User(String name, String email) {
            this.name = name;
            this.email = email;
        }
    }

My Dao:

@Dao
public interface UserDao {
    @Insert
    void insertAll(User... users);

    // Not working
    @Query("SELECT DISTINCT user_name FROM User")
    List<User> fetchDistinctUser();
    
    // Working
    @Query("SELECT * FROM User")
    List<User> fetchAllUser();
}

Let me know if I am missing something.

If I changed List<User> to List<String> it's working but What if we want other details as well.

Original:

// Not working
  @Query("SELECT DISTINCT user_name FROM User")
  List<User> fetchDistinctUser();

Changed:

// Working
  @Query("SELECT DISTINCT user_name FROM User")
  List<String> fetchDistinctUser();

But still, the problem is there How to fetch other details?

Your query is Right. but why not working I can not understand. You can alternatively check when you insert data. @Insert(onConflict = OnConflictStrategy.REPLACE) void insertAll(User... users); OR @Insert(onConflict = OnConflictStrategy.IGNORE) void insertAll(User... users); — Shohel Rana, May 22 '19 at 10:30
@ShohelRana as suggested in the answer if I changed the List to List It's working but what if we want other details as well as — Harsh Shah, May 22 '19 at 10:57
if you have not got the full object of list. You duplicate data check during Insert time. that is already said to you. then get all data Using the select query. You can use that for alternatively — Shohel Rana, May 22 '19 at 11:14

score 10 · Answer 1 · answered Jan 03 '20 at 11:33

10

You can solve this issue using Group By

@Query("SELECT user_name FROM User group by userID")
List<User> fetchDistinctUser();

Like the above example, you will get distinct users.

answered Jan 03 '20 at 11:33

Chetan Chaudhari

323
5
15

Where is userID in OP question? – androidStud Jan 13 '20 at 03:32
it is id in User entity. – Chetan Chaudhari Jan 21 '20 at 07:17
the question isn't about selecting the userID, it is about SELECT DISTINCT user_name which this solution doesn't solve!!!! – Shady Mohamed Sherif Feb 10 '22 at 11:44

Said · Answer 2 · 2019-05-22T10:44:42.573

2

Use property from User:

@Query("SELECT DISTINCT name FROM User")
List<String> fetchDistinctUser();

edited May 22 '19 at 10:44

answered May 22 '19 at 10:12

Said

689
6
20

Can you copy log? – Said May 22 '19 at 10:40
Sorry, I've updated answer. If you want distinct names, than result should be List instead of List – Said May 22 '19 at 10:45
It's working but What is the use of this I want all other information along with the name. Do you know how to achieve this? – Harsh Shah May 22 '19 at 10:55
For example, you have a table (id, user, city) with data (1,john,london) and (2,john,tokio). What you expect with your query, london or tokio? You can use group by if it isn't important. – Said May 22 '19 at 11:00
I need id...i.e (1 and 2 ) based on that rest of the feature need id – Harsh Shah May 22 '19 at 11:06

score 2 · Answer 3 · answered Nov 29 '19 at 11:14

You are taking a hole recordset which is already distinct because your id is primary which are distinct.

You just remove id as primary or use can take another class for recordset which does not contain id fields.

eg.

  @Query("SELECT name,email FROM User where name in(select distinct name from User)")
    List<UserNew> fetchDistinctUser();

score 0 · Answer 4 · answered Jan 31 '20 at 13:46

0

For anyone coming here with the same problem I had, Select distinct in room does not work with LiveData. I am not sure why but It's probably a bug.

answered Jan 31 '20 at 13:46

Rami Jemli

2,550
18
31

sagar · Answer 5 · 2021-01-01T06:24:45.207

if you want other details from the distinct user_name you can do in following way:

First way:

  @Query("SELECT * FROM User where user_name in (SELECT DISTINCT user_name FROM User)")
  List<User> fetchDistinctUser();

Second way:

step 1: first get distinct user name as:

  @Query("SELECT DISTINCT user_name FROM User")
  List<String> fetchDistinctUser();

step 2: Once you get distinct user name in your activity or in any class \ function, pass it again to the Dao in order to get all data as:

  @Query("SELECT * FROM User WHERE user_name LIKE :name")
  List<User> fetchAllDataOfDistinctUser(String name);

Room Database distinct value

5 Answers5