Search code examples
javaandroidsqlsqlitedao

How to insert selected result from a table into another table on Android Room's DAO class

I've been developing a budget spending tracking app on Android Studio. In this particular part, what I want to do is to insert the selected result of a table "budget_tracker_table" into another table "budget_tracker_table_alias". The SQL I coded worked without any problem when I executed it in App Inspection, when I put the exact same line in the DAO class, it displays the error, saying "Cannot find method 'value'". Is there any possible solution to this problem? Thanks.

@Insert("insert into budget_tracker_table_alias (date_alias, store_name_alias, product_name_alias, product_type_alias, price_alias, product_type_percentage) select date, store_name, product_name, product_type, price, count(*) * 100.0 / (select count(*) from budget_tracker_table where date >= '2022-4-22' and date <= '2022-4-23') as 'Percentage' from budget_tracker_table where date >= '2022-4-22' and date <= '2022-4-23' group by store_name;")
void insert(BudgetTrackerAlias budgetTrackerAlias);
Solution

  • Room's @Insert is a convenience insert, it expects an object (or list of objects) only and that object being an object of the type/class of the respective entity.

    You need to use @Query with the SQL to insert, so

    @Query("insert into budget_tracker_table_alias (date_alias, store_name_alias, product_name_alias, product_type_alias, price_alias, product_type_percentage) select date, store_name, product_name, product_type, price, count(*) * 100.0 / (select count(*) from budget_tracker_table where date >= '2022-4-22' and date <= '2022-4-23') as 'Percentage' from budget_tracker_table where date >= '2022-4-22' and date <= '2022-4-23' group by store_name;")
void insert();

    Of course that would likely be of limited use as the dates are hard coded if you wanted to pass the dates then you could have:-

    @Query("insert into budget_tracker_table_alias (date_alias, store_name_alias, product_name_alias, product_type_alias, price_alias, product_type_percentage) select date, store_name, product_name, product_type, price, count(*) * 100.0 / (select count(*) from budget_tracker_table where date >= :fromDate and date <= :toDate) as 'Percentage' from budget_tracker_table where date >= :formDate and date <= :toDate group by store_name;")
void insert(String fromDate,String toDate);

    P.S. same with @Delete and @Update, that is they expect the object but you can use @Query to utilise UPDATE/DELETE SQL