# RawQuery

Artifact: [androidx.room3:room3-common](https://developer.android.com/jetpack/androidx/releases/room3) [View Source](https://cs.android.com/search?q=file:androidx/room3/RawQuery.kt+class:androidx.room3.RawQuery) Added in [3.0.0](https://developer.android.com/jetpack/androidx/releases/room3#3.0.0)

*** ** * ** ***

Common/AllAndroid/JVMNative/C/iOSJavaScript
- Cmn

  ```
  @Target(allowedTargets = [AnnotationTarget.FUNCTION])
  @Retention(value = AnnotationRetention.BINARY)
  annotation RawQuery
  ```

*** ** * ** ***

Marks a function in a `https://developer.android.com/reference/kotlin/androidx/room3/Dao` annotated class as a raw query function where the query is pass as a parameter of types `https://developer.android.com/reference/kotlin/androidx/room3/RoomRawQuery` or androidx.sqlite.db.SupportSQLiteQuery.

```kotlin
@Dao
interface RawDao {
    @RawQuery
    suspend fun getSongViaQuery(query: RoomRawQuery): Song
}

// Usage of RawDao
val query = RoomRawQuery(
    sql = "SELECT * FROM Song WHERE id = ? LIMIT 1",
    onBindStatement = { it.bindLong(1, songId) }
)
val song = rawDao.getSongViaQuery(query)
```

Room will generate the code based on the return type of the function and failure to pass a proper query will result in a runtime failure or an undefined result.

If you know the query at compile time, you should always prefer `https://developer.android.com/reference/kotlin/androidx/room3/Query` since it validates the query at compile time and also generates more efficient code, since Room can compute the query result at compile time (e.g. it does not need to account for possibly missing columns in the result).

On the other hand, `@RawQuery` serves as an escape hatch where you can build your own SQL query at runtime but still use Room to convert it into objects.

`@RawQuery` functions must return a value and their return type should be a non-void / non-`https://kotlinlang.org/api/core/kotlin-stdlib/kotlin/-unit/index.html` type.

`@RawQuery` functions can only be used for read queries, using a write query will lead to undefined behavior. For write queries, use `https://developer.android.com/reference/kotlin/androidx/room3/package-summary#(androidx.room3.RoomDatabase).useWriterConnection(kotlin.coroutines.SuspendFunction1)`:

```kotlin
suspend fun executeRawQuery(query: RoomRawQuery) {
    db.useWriterConnection { connection ->
        connection.usePrepared(query.sql) { statement ->
            query.getBindingFunction().invoke(statement)
            statement.step()
        }
    }
}
```

**Observable Queries:**

`@RawQuery` functions can return observable types such as `https://kotlinlang.org/api/kotlinx.coroutines/kotlinx-coroutines-core/kotlinx.coroutines.flow/-flow/index.html` but you need to specify which tables are accessed in the query using the `https://developer.android.com/reference/kotlin/androidx/room3/RawQuery#observedEntities()` annotation value.

```kotlin
@Dao
interface RawDao {
    @RawQuery(observedEntities = Song::class)
    fun getSongs(query: RoomRawQuery): Flow<List<Song>>
}

// Usage of RawDao
val liveSongs = rawDao.getSongs(
    RoomRawQuery("SELECT * FROM song ORDER BY name DESC")
)
```

**Returning Data Objects:**

`@RawQuery` can also return data objects, similar to `https://developer.android.com/reference/kotlin/androidx/room3/Query` methods.

```kotlin
data class NameAndReleaseYear (
    val name: String,
    @ColumnInfo(name = "release_year")
    val year: Int
)

@Dao
interface RawDao {
    @RawQuery
    suspend fun getNameAndReleaseYear(query: RoomRawQuery): NameAndReleaseYear
}

// Usage of RawDao
val result: NameAndReleaseYear = rawDao.getNameAndReleaseYear(
    RoomRawQuery(
        sql = "SELECT * FROM song WHERE id = ?",
        onBindStatement = { it.bindLong(1, songId) }
    )
)
```

**Data Objects with Embedded Properties:**

`@RawQuery` methods can return data objects that include `https://developer.android.com/reference/kotlin/androidx/room3/Embedded` properties as well.

```kotlin
data class SongAndArtist (
    @Embedded
    val song: Song,
    @Embedded
    val artist: Artist
)

@Dao
interface RawDao {
    @RawQuery
    suspend fun getSongAndArtist(query: RoomRawQuery): SongAndArtist
}

// Usage of RawDao
val result: = rawDao.getSongAndArtist(
    RoomRawQuery("SELECT * FROM Song, Artist WHERE Song.artistId = Artist.id LIMIT 1")
)
```

**Relations:**

`@RawQuery` return types can also be objects with `https://developer.android.com/reference/kotlin/androidx/room3/Relation`.

```kotlin
data class AlbumAndSongs {
    @Embedded
    val album: Album,
    @Relation(parentColumn = "id", entityColumn = "albumId")
    val songs: List<Song>
}

@Dao
interface RawDao {
    @RawQuery
    suspend fun getAlbumAndSongs(query: RoomRawQuery): List<AlbumAndSongs>
}

// Usage of RawDao
val result = rawDao.getAlbumAndSongs(
    RoomRawQuery("SELECT * FROM album")
)
```

## Summary

| ### Public constructors |
|---|---|
| `https://developer.android.com/reference/kotlin/androidx/room3/RawQuery#RawQuery(kotlin.Array)(observedEntities: https://kotlinlang.org/api/core/kotlin-stdlib/kotlin/-array/index.html<https://kotlinlang.org/api/core/kotlin-stdlib/kotlin.reflect/-k-class/index.html<*>>)` | Cmn |

| ### Public properties |
|---|---|---|
| `https://kotlinlang.org/api/core/kotlin-stdlib/kotlin/-array/index.html<https://kotlinlang.org/api/core/kotlin-stdlib/kotlin.reflect/-k-class/index.html<*>>` | `https://developer.android.com/reference/kotlin/androidx/room3/RawQuery#observedEntities()` Denotes the list of entities which are accessed in the provided query and should be observed for invalidation if the query is observable. | Cmn |

## Public constructors

### RawQuery

Cmn

```
RawQuery(observedEntities: Array<KClass<*>> = [])
```

## Public properties

### observedEntities

Cmn

```
val observedEntities: Array<KClass<*>>
```

Denotes the list of entities which are accessed in the provided query and should be observed for invalidation if the query is observable.

The listed classes should either be annotated with `https://developer.android.com/reference/kotlin/androidx/room3/Entity` or they should reference at least one `https://developer.android.com/reference/kotlin/androidx/room3/Entity` (via `https://developer.android.com/reference/kotlin/androidx/room3/Embedded` or `https://developer.android.com/reference/kotlin/androidx/room3/Relation`).

Providing this value in a non-observable query has no impact.

```kotlin
@Dao
interface RawDao {
    @RawQuery(observedEntities = Song::class)
    suspend fun getSongs(query: RoomRawQuery): Flow<List<Song>>
}
val liveSongs: = rawDao.getSongs(
    RoomRawQuery("SELECT * FROM song ORDER BY name DESC")
)
```

| Returns |
|---|---|
| `https://kotlinlang.org/api/core/kotlin-stdlib/kotlin/-array/index.html<https://kotlinlang.org/api/core/kotlin-stdlib/kotlin.reflect/-k-class/index.html<*>>` | List of entities that should invalidate the query if changed. |