Room: How to perform query based on composite primary keys?

Is querying using composite primary key possible with Room?

@Entity(
    tableName = "market_cap",
    primaryKeys = ["assetId", "timestamp"]
)
data class MarketCapDataEntity(
    val assetId: String,
    val timestamp: Long,
    val price: Double
)

Let say I want to perform query by primary key like below?

@Query("SELECT * FROM market_cap WHERE primaryKeys = :compositeKey")
suspend fun getMarketCapByAssetId(compositeKey: String): List<MarketCapDataEntity>

Is querying using composite primary key possible with Room?

No (see below) BUT that is something that you would/could not query by.

Basically the purpose of an index is for the underlying system (the query optimizer) to optimally search the database.

As such you never really query by an index but by columns that may or may not form part of an index.

As such for @Query's you simply specify the column(s) for the query. There is no single representation for a composite index that can be used.

So @Query("SELECT * FROM market_cap WHERE primaryKeys = :compositeKey") would result in an error as primaryKeys will be considered to be a column name which would not exist.

Instead you would use something like:-

@Query("SELECT * FROM market_cap WHERE assetId = :assetId AND timestamp = :timestamp")

• this would very likely use the index on the Primary Key for the search through the table (typically the query optimiser decides)
  • however, there will ALWAYS (for Room) be another hidden/invisible index, it being based upon the normally hidden rowid column (All Room tables, defined via @Entity annotation, will be ROWID tables). In some cases this may well be the optimal index as SQLite is written to favour the rowid, which can be up to twice as fast to process as other indexes. You may find https://sqlite.org/rowidtable.html useful.

Convenience annotation and the Primary Key(s)

It should be noted that some of the convenience annotation, i.e. @Update and @Delete do utilise and require the values of the field or fields that the primary key is comprised of.

So when/if using either then it is important that the object passed to the annotated function/method has the primary key field values set to the appropriate values (noting that you cannot update the primary key values as that would then not locate the row).

• If you need to get around the limitations/restrictions of the convenience then you can use the @Query annotation with appropriate SQL e.g.

@Query("UPDATE market_cap SET assetId=:newAssetId, timestamp=:newTimeStamp WHERE assetId=:originalAssetId AND timestamp=originalTimeStamp")

• obviously you pass the 2 pairs of assetId and Timestamp as the parameter names suggest.