Multi tool use

How to access object joined with SQL using Room on Android

I want to ask you for advice, how can I access object after using LEFT JOIN on two tables. I've got tables defined in external file File.db and I'm loading it to Room database on Android. I've got two tables defined:

LEFT JOIN

CREATE TABLE Example (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
`name` TEXT NOT NULL,
`description` TEXT,
`source_url` TEXT
);

CREATE TABLE Example_dates (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
`example_id` INTEGER NOT NULL,
`color` INTEGER NOT NULL,
`date_from` TEXT,
`date_to` TEXT,
FOREIGN KEY(`example_id`) REFERENCES `Example`(`id`)
);


My entities are:

@Entity(
tableName = "Example"
)
data class Example constructor(
@PrimaryKey @ColumnInfo(name = "id") var id: Int,
@ColumnInfo(name = "name") var name: String,
@ColumnInfo(name = "description") var description: String?,
@ColumnInfo(name = "source_url") var sourceUrl: String?
)

@Entity(
tableName = "Example_dates",
foreignKeys = arrayOf(
ForeignKey(entity = Example::class, parentColumns = ["id"],
childColumns = ["example_id"]))
)
data class Example_dates constructor(
@PrimaryKey @ColumnInfo(name = "id") var id: Int,
@ColumnInfo(name = "example_id") var exampleId: Int,
@ColumnInfo(name = "color") var color: Int,
@ColumnInfo(name = "date_from") var dateFrom: String?,
@ColumnInfo(name = "date_to") var dateTo: String?
)


Dao object:

@Dao
interface AnimalDao {
@Query(
"SELECT * FROM example_dates LEFT JOIN example ON example_dates.example_id = example.id")
fun loadAll(): Cursor
}


And I'm building DB like this:

RoomAsset
.databaseBuilder(context, AppDatabase::class.java, "File.db")
.build()


Is there any way, how to get merged data from SQL statement in different way then Cursor? I've tried to add more fields to data class Example constructor annotated with @Ignore but I got error with differences in table - "Expected/Found". Or is solution based on cursor the right way of implementation?

data class Example

@Ignore

Thank you.

1 Answer
1

Ok, as official documentation said https://developer.android.com/training/data-storage/room/accessing-data

"It's highly discouraged to work with the Cursor API because it doesn't guarantee whether the rows exist or what values the rows contain."

so I tried to create another data class named ExampleDetail with all fields I need and in DAO object I'm returning List instead of Cursor.

data class named ExampleDetail

DAO

Thank you.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.