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?

Makeup

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); – Mischa

@ShohelRana, thanx I will try this – Makeup

@ShohelRana as suggested in the answer if I changed the List<User> to List<String> It's working but what if we want other details as well as – Makeup

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 – Mischa

Answer 1

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.

Castlereagh

Where is userID in OP question? – Confederate

it is id in User entity. – Castlereagh

the question isn't about selecting the userID, it is about SELECT DISTINCT user_name which this solution doesn't solve!!!! – Zorina

Answer 2

Use property from User:

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

Skillful

Can you copy log? – Skillful

Sorry, I've updated answer. If you want distinct names, than result should be List<String> instead of List<User> – Skillful

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? – Makeup

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. – Skillful

I need id...i.e (1 and 2 ) based on that rest of the feature need id – Makeup

Answer 3

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

Jemimah

Answer 4

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.

Lab

Answer 5

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

Whoever