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?

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.