skip to content

In Room, when would you return Map<Artist, List<Album>> from a JOIN query instead of an @Relation result class?

level: middleimportance: should knowfreq 28%

answer

  1. no extra data class
  2. your SQL does the joining
  3. one statement, rows grouped by key
  4. @MapColumn for single-column keys or values

basics

~20 s

Room's multimap return type groups one JOIN query's rows into a Map, so SQL can filter, order and aggregate across both tables. Choose it when the screen needs that, or a count via @MapColumn; choose @Relation for nested reads without hand-written joins.

solid answer

~40 s

A DAO function can return a multimap such as `Map<Artist, List<Album>>` from `SELECT * FROM Artist JOIN Album ON ...`; Room runs that one statement and groups the rows by the key object. Because the SQL is yours, `WHERE`, `GROUP BY ... HAVING` and an `ORDER BY` on album columns all work, which `@Relation` cannot do for its children. For a single-column key or value, such as a count, annotate the type argument: `Map<Artist, @MapColumn(columnName = "albumCount") Int>`. Room 2.6.0 introduced `@MapColumn` and deprecated the function-level `@MapInfo`, which Room 3.0 does not have. Remember that an `INNER JOIN` drops artists without albums, and that the join repeats artist columns on every album row. `@Relation` stays the better fit for nested graphs.

code

kotlin · 20 lines
kotlin
@Dao
interface CatalogDao {
    @Query(
        """
        SELECT * FROM Artist
        JOIN Album ON Album.artistOwnerId = Artist.artistId
        ORDER BY Artist.name, Album.year DESC
        """
    )
    suspend fun artistsWithAlbums(): Map<Artist, List<Album>>

    @Query(
        """
        SELECT Artist.*, COUNT(Album.albumId) AS albumCount
        FROM Artist LEFT JOIN Album ON Album.artistOwnerId = Artist.artistId
        GROUP BY Artist.artistId
        """
    )
    suspend fun albumCounts(): Map<Artist, @MapColumn(columnName = "albumCount") Int>
}

go deeper

for a junior

Recall that a DAO function can return Map<Artist, List<Album>> from a join query, with no extra result class.

for a middle

Explain what the multimap can do that @Relation cannot (filter, order and aggregate children in SQL) and when @MapColumn is required.

for a senior

Show the traps: INNER JOIN dropping parents, same-named columns, repeated parent data, and migrating @MapInfo to @MapColumn before Room 3.0.

for a principal

Set a team default between multimaps and relation classes per read shape, documenting when nested relations or wide parents favour @Relation.

## Two ways to read related rows Room offers two shapes for "an artist together with their albums": - **A relation result class**: `ArtistWithAlbums(@Embedded artist, @Relation(...) albums)`. Your query selects only `Artist`; Room fetches the albums with its own generated queries. - **A multimap return type**: the DAO function returns `Map<Artist, List<Album>>` and your query joins the tables itself. Room runs that **single statement**, reads each row into an `Artist` and an `Album`, and groups the albums under their artist. Room's relationships guide frames the trade for joined results: a multimap makes your SQL do more work, while extra intermediate data classes make your code do more work, and without a specific reason for those classes it recommends multimaps. `@Relation` is a third route, where Room writes the child queries for you. ## What a multimap query can do that `@Relation` cannot Because the join is hand-written, everything SQL offers applies across both tables: - **Filter by the children**: `GROUP BY Artist.artistId HAVING COUNT(Album.albumId) >= 3` returns only artists with three or more albums. - **Order the children**: `ORDER BY Artist.name, Album.year DESC` orders albums inside each artist. `@Relation` has no ordering parameter, and its child query is generated for you. Room keeps the query's row order when it builds the map (a 2.4.0 fix made an `ORDER BY` on key columns hold). - **Aggregate**: map an artist to a computed value instead of rows. - **Read junction attributes**: a join through a playlist cross-reference can order songs by its `position` column. ## Single-column keys and values: `@MapColumn` When the key or value is a single column of a built-in type (a primitive, `String`, an enum, `ByteArray`) or of a type with a column converter, Room needs to know which column to use. Annotate the **type argument**: | Return type | Meaning | |---|---| | `Map<Artist, List<Album>>` | whole rows on both sides, no annotation needed | | `Map<Artist, @MapColumn(columnName = "albumCount") Int>` | each artist mapped to a computed count | | `Map<@MapColumn(columnName = "name") String, List<@MapColumn(columnName = "title") String>>` | artist names mapped to album titles | `@MapColumn` also takes `tableName` to pick between same-named columns from different tables or aliases. Its history matters when reading older code: 1. Room 2.4.0 added multimap return types. 2. `@MapInfo(keyColumn = ..., valueColumn = ...)`, a function-level annotation, filled the single-column role. 3. Room 2.6.0 added `@MapColumn` on the type argument and **deprecated** `@MapInfo`. 4. Room 3.0 (`androidx.room3`) has no `@MapInfo` at all; only `@MapColumn` remains. ## The traps - **`INNER JOIN` drops parents without children.** An artist with no albums produces no joined row, so it is absent from the map. For "every artist with their album count", use `LEFT JOIN` with `COUNT`, as Room's documentation does. - **Same-named columns.** `Artist.id` and `Album.id` both arrive as `id`. Since Room 2.5.0 Room resolves such ambiguous columns heuristically, from the order of the result columns; distinct column names, or `@MapColumn`'s `tableName`, remove the guesswork. - **Repeated parent data.** A join returns the artist's columns once per album row. For a few albums that is nothing; for wide parent rows and many children it is more data than `@Relation`'s separate queries read. - **One level only.** A multimap groups one join; a three-level artist, album and song tree is clumsy as nested maps and natural as nested `@Relation` classes. ## Transactions and observation A multimap query is one statement, so it does not need `@Transaction` to keep a parent query and child query consistent the way a relation read does. The `@Transaction` documentation keeps one other case: with a driver over Android's own SQLite bindings, a very large result read outside a transaction can be corrupted by writes between cursor windows. A multimap can also be returned through a `Flow`; how that re-emits is covered with Room's coroutine and Flow support. ## Choosing Pick the **multimap** when one join answers the screen and SQL must filter, order or aggregate across the tables. Pick **`@Relation`** when the read is a nested graph, when you want each child list complete without writing joins, or when wide parent rows would repeat across many child rows. | Need | Better fit | |---|---| | Filter parents by their children (`HAVING`) | multimap | | Children in a specific order | multimap | | Counts or other aggregates per parent | multimap with `@MapColumn` | | Three-level graph, each list complete | nested `@Relation` | | Wide parent rows, many children each | `@Relation` | Many apps use both: relation classes for detail screens that show a whole album with its tracks, multimaps for list screens that filter, sort and count.

  • You migrate a Room 2.5 DAO that uses `@MapInfo(valueColumn = "albumCount")` to Room 3.0. What changes?
    Remove the function-level `@MapInfo` and annotate the map's value type argument instead: `Map<Artist, @MapColumn(columnName = "albumCount") Int>`. `@MapColumn` replaced it in Room 2.6.0, where `@MapInfo` became deprecated, and Room 3.0 has no `@MapInfo` at all.
  • Artist and Album both have a column called `id`. How does Room map a `SELECT *` join to `Map<Artist, List<Album>>`?
    Since Room 2.5.0 it resolves ambiguous columns heuristically, matching each object's columns to contiguous runs in the result. It usually works, but naming keys distinctly, or using `@MapColumn`'s `tableName` for single-column maps, removes the reliance on a heuristic.

  • A multimap query needs @Transaction for the same reason a @Relation read does
  • @MapInfo on the function is the current way to map a count column
  • An INNER JOIN multimap still returns artists with no albums
  • @Relation can order child rows through the parent query's ORDER BY
  • Multimaps make @Relation unnecessary even for three-level nested reads

Assumes Room 3.0, which keeps multimap returns and @MapColumn but has no @MapInfo; Room 2.6.0 deprecated @MapInfo in favour of @MapColumn, and multimaps arrived in 2.4.0.