Room: How to get albums and all songs with single DAO method

Question

Asuming a database schema with Artist, Album, and Song, where an Artist can have many Albums and an Album can have many songs, is there a way to define a DAO method that would get me a list of all Albums and all Songs of each Album?

I know I can get Song and Album with something like this:

@Query("SELECT * FROM Song s JOIN Album a ON s.albumId = a.id")
List<SongAndAlbum> loadSongAndAlbum();

but I have no idea how to define a query that would provide a list of all Albums and all Songs of seach Album.

score 0 · Accepted Answer · answered Aug 09 '20 at 12:38

Let's say you have such a scheme:

@Entity
public class Artist {
    @PrimaryKey public long Id;
    public String name;
}

@Entity
public class Album {
    @PrimaryKey public long Id;
    public String name;
    public int artistId;
}
@Entity
public class Song {
    @PrimaryKey public long Id;
    public String name;
    public int albumId;
}

To get List with Albums with each album holding List of songs you can use Room's Relations:

public class AlbumWithSongs {
    @Embedded public Album album;
    @Relation(
         parentColumn = "id",
         entityColumn = "albumId"
    )
    public List<Song> songs;
}

Your dao method should be:

@Query("SELECT * FROM Album")
List<AlbumWithSongs> loadAlbumWithSongs();

If in addition to that you want to get List with Artists with each artist holding list of albums you should add one more class:

public class ArtistWithAlbums {
    @Embedded public Artist artist;
    @Relation(
         entity = Album.class,
         parentColumn = "id",
         entityColumn = "artistId"
    )
    public List<AlbumWithSongs> albums;
}

Your dao method for that should be:

@Query("SELECT * FROM Artist")
List<ArtistWithAlbums> loadArtistWithAlbums();

Room: How to get albums and all songs with single DAO method

1 Answers1