CODEWITHSUNDEEP
androidandroid-sqliteandroid-roomandroid-architecture-components
Akash Patra
Akash Patra

Reputation: 768

Error in Room Query to Return Subset of Columns

I was playing with Room, where I couldn't find some solution to my queries.

Below is the data.

Table

CREATE TABLE `Employee` (
    `id` INTEGER NOT NULL,
    `first_name` TEXT,
    `last_name` TEXT,
    PRIMARY KEY(`id`)
);

Table Data

enter image description here

Entity

@Entity(tableName = "Employee")
public class Employee {

    @PrimaryKey
    private int id;

    @ColumnInfo(name = "first_name")
    private String firstName;

    @ColumnInfo(name = "last_name")
    private String lastName;

    ..Getters & Setters..
}

Query 1

@Query("Select * from Employee")
List<Employee> getEmployees();

Result Its successfull

Query 2

@Query("Select first_name, last_name from Employee")
List<Employee> getEmployees();

Result

error: The columns returned by the query does not have the fields [id] in ***.Employee even though they are annotated as non-null or primitive. Columns returned by the query: [first_name, last_name]

If I add id to above Query 2, it works.

Same goes, if we have a Foreign Key in the Table and we try to Query Subset of Columns, it throws Error. The Error goes when we add both Primary Key & Foreign Key Column in the Query.

Question 1 Does that mean we have to always include Primary Key & Foreign Key (if present) in a Query ?

Question 2 What actually happens under the hood that it throws such error ? Or Am I doing anything wrong ?

Room Version 1.1.1

Also, referred this link but it doesn't solve my issue with Primary Keys.

Upvotes: 5

Views: 6782

Answers (3)

Monster Brain
Monster Brain

Reputation: 2119

Adding to @karan's answer, the pojo can also be used to return named columns using as clause

For eg.

select sum(score) as total_score, sum(avg) as avg_score from mytable

where the pojo class column names will be total_score and avg_score respectively ..

Upvotes: 1

karan
karan

Reputation: 8853

To select data from multiple fields consider below example.

From the docs

Room allows you to return any Java-based object from your queries as long as the set of result columns can be mapped into the returned object. For example, you can create the following plain old Java-based object (POJO) to fetch the user's first name and last name:

public class NameTuple {
    @ColumnInfo(name = "first_name")
    public String firstName;

    @ColumnInfo(name = "last_name")
    public String lastName;
}

Now, you can use this POJO in your query method:

@Dao
public interface MyDao {
    @Query("SELECT first_name, last_name FROM user")
    public List<NameTuple> loadFullName();
}

Upvotes: 13

Himanshu
Himanshu

Reputation: 3970

I think you should include in
@ColumnInfo(name = "id") @PrimaryKey private int id;

because as per the error thrown there is no such column existing as id in your entity by the above query, first the column id is referred as match of the tables column id and then set a primary key via annotation

Upvotes: 0

Related Questions