android - How to combine columns from Android Room LEFT OUTER JOIN select statement

Question

Im employing Room in my current Android application.

I have a a main data table with over 20 columns.

One of these columns has a code that has associated reference db table that has the display description for the code.

I would like to join the reference data table to the main data table to allow me to display the description instead of the code.

currently my select statement is "select * from main_data_table...."

To achieve my desired result is my only option to replace "select *" with a list of all 20+ column names? switching the code column name for the code_description column name?

or is there a "Short Hand" mechanism I can employ?

标签： androidandroid-sqliteandroid-room

Answer 1

The typical way, would be rather than to try to replace the value in the base (Entity) object (perhaps a main_data_table object) by renaming columns, to instead have a POJO that includes additional variables.

Assuming the objects are Main_data_table and Reference_data then you could have

data class main_data_table_with_reference_data(
    @Embedded
    val main_data_table: Main_data_table,
    @Embedded
    val reference_data: Reference_data
) 

• Note that this relies upon no column names being the same in the two tables. If there are duplicated column names, then matters can be a little more complicated. You can use the prefix parameter of the @Embedded to specify a prefix e.g. @Embedded(prefix = "myprefix") for the column names BUT then you have to ensure that the query names those columns using appropriate AS clauses.

• I would always suggest ensuring that ALL column names are unique, that would eliminate the need for overly long queries.

You don't have to Embed objects, you can use variables BUT again the column names generated by the query should match the variable names in the POJO (it is fine for the query to output more columns but the query must output all the variables in the POJO)