Room Database distinct value

how to check if data is inserted in room database
android-room unique
pre populate room database
android room nested objects
android-room ignore field
room database structure
android room nullable field
android-room check if record exists

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<Sttring> it's working but What if we want to 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?


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.

SQLite SELECT DISTINCT, If you use the DISTINCT clause with a column that has NULL values, SQLite will keep one row of a NULL value. In database theory, if a column contains NULL� So for each distinct value in a column of one table I want to insert that unique value into a row of another table. list = select distinct(id) from table0 for distinct_id in list insert into table1 (id) values (distinct_id) end Any ideas as to how to go about this?


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

Defining data using Room entities, Call the addCallback method when building your RoomDatabase as Room does not yet allow setting default values for columns. You need to make sure that your DAO filters emissions and only reacts to distinct objects. The SQL SELECT DISTINCT Statement. The SELECT DISTINCT statement is used to return only distinct (different) values. Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values. SELECT DISTINCT Syntax


Use property from User:

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

7 Pro-tips for Room. Learn how you can get the most out of…, Powering on, let's take a look at the DISTINCT clause in SQL: /* * DISTINCT you only want to return a subset of rows which have distinct values for that column. Distinct is an SQL command that helps to return only the distinct values. A column in a table can have the same value multiple times. In other words, it can contain duplicate values. Sometimes, the programmer may need to remove those duplicate values and to obtain distinct values available in the database.


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.

DISTINCT and LIMIT clauses, SQLite DISTINCT keyword is used in conjunction with SELECT statement to eliminate all the duplicate records and fetching only the unique records. There may� SELECT DISTINCT retrieves unique values from fields which can help with an array of tasks that can vary from reporting to locating errors in database input. Ready to explore SQL reporting services? Not only does SELECT DISTINCT help with retrieving the data being requested, it can also help with ensuring that output is displayed in a way that is easier to read.


SQLite - DISTINCT Keyword, To write SQL queries in an SQLite database, you have to know how the A literal value is a constant value that can be specified in the select statement. You can The following query will concatenate the distinct values of the� Sometimes, you may want to get only distinct values in a specified column of a table. To do this, you use the SELECT DISTINCT clause as follows: SELECT DISTINCT column_name FROM table_name; The query returns only distinct values in the specified column.


SQLite Query: Select, Where, LIMIT, OFFSET, Count, Group By, Answer to a)Again, work on the room table created in the last week with the records as follows: b) Select the distinct value of gu The DISTINCT keyword corresponds to the Design View property Unique Values = True. Save that query as, for example, CompanySites. Now count values in the saved query: SELECT Company, Count(Site) As Sites FROM CompanySites


Solved: A)Again, Work On The Room Table Created In The Las , Room database, and I want to get two columns values(Category and total amount) from IncomeExpense table by comparing distinct values in� A UNIQUE constraint ensures all values in a column or a group of columns are distinct from one another or unique. To define a UNIQUE constraint, you use the UNIQUE keyword followed by one or more columns. You can define a UNIQUE constraint at the column or the table level. Only at the table level, you can define a UNIQUE constraint across multiple columns.