There are 3 entities in my Room DB:
Album, PhotosMediaItem and VideosMediaItem.
VideosMediaItem and PhotosMediaItem inherit from MediaItem.
MediaItem is not an entity in the DB, it's just an abstract base class.
I would like to create a query that returns all the photos and videos media items in a specific album with descending order based on their creation date.
So the query will create a list of MediaItems but with the derived types. (PhotoMediaItem or VideoMediaItem) in a polymorphic way.
Here's what I've tried:
@Query("SELECT * FROM PhotosMediaItem WHERE PhotosMediaItem = :albumId " +
"UNION SELECT * FROM VideosMediaItem WHERE VideosMediaItem = :albumId" +
" ORDER by CreationDate DESC")
List<MediaItem> getAllMediaInAlbum(int albumId);
This won't work obviously, because it tries to initiate MediaItem object, and it is not my intention. I want this query to initiate the derived class, PhotoMediaItem or VideoMediaItem
Here's how my query looked like before the migration to Room, using the regular SQLiteHelper, and it worked just fine:
public ArrayList<MediaItem> getMediaListByAlbumId(int palbumId)
{
Cursor cursor = null;
try{
ArrayList<MediaItem> mediaList = new ArrayList<>();
String selectQuery = "SELECT "+ mPhotoId +","+ mPhotoCreationDate +", 0 AS mediaType, '' FROM "+ mPhotosTableName + " WHERE " + this.mPhotoAlbumId + "="+palbumId +
" UNION " +
"SELECT "+ mVideoId +","+ mVideoCreationDate + " ,1 AS mediaType, " + mVideoLength + " FROM " + mVideosTableName + " WHERE " + this.mVideoAlbumId +"="+palbumId +
" ORDER BY CreationDate DESC";
cursor = mDB.rawQuery(selectQuery, null);
// looping through all rows and adding to list
if (cursor.moveToFirst()){
do {
// MediaHolder consists of the media ID and its type
int mediaType = cursor.getInt(2);
MediaItem mediaItem = null;
if (mediaType == 0) {
mediaItem = new PhotoMediaItem(cursor.getInt(0), null, palbumId);
} else if (mediaType == 1) {
mediaItem = new VideoMediaItem(cursor.getInt(0), null, palbumId, cursor.getLong(3));
}
mediaList.add(mediaItem);
}
while (cursor.moveToNext());
}
return mediaList;
}
finally {
if(cursor != null){
cursor.close();
}
}
}
How can I achieve the same effect using Room then?
I think you have a number of options here:
Option 1
You use a single table to store all MediaItems and you use a discriminator column to make the difference between a video and a photo.
You have a single DAO method that performs the query, applies the order by and returns a Cursor. Then you can use your existing cursor manipulation logic to return a List<MediaItem>
It can look like this:
@Dao
public abstract class MediaItemDao() {
@Query("you query here")
protected Cursor getByAlbumIdInternal(int albumId);
public List<MediaItem> getByAbumId(int albumId) {
Cursor cursor = null;
try{
List<MediaItem> mediaList = new ArrayList<>();
cursor = getByAlbumIdInternal(albumId);
// looping through all rows and adding to list
if (cursor.moveToFirst()){
do {
// use the discriminator value here
int mediaType = cursor.getInt(cursor.getColumnIndex("you discriminator column name here"));
MediaItem mediaItem = null;
if (mediaType == 0) {
mediaItem = new PhotoMediaItem(cursor.getInt(0), null, palbumId);
} else if (mediaType == 1) {
mediaItem = new VideoMediaItem(cursor.getInt(0), null, palbumId, cursor.getLong(3));
}
mediaList.add(mediaItem);
} while (cursor.moveToNext());
}
return mediaList;
}
finally {
if(cursor != null){
cursor.close();
}
}
}
}
Option 2
You use two different tables to store VideosMediaItem and PhotosMediaItem. You have a MediaItemDao that has two internal methods to perform the queries and a single public method that merges the two result sets together and applies the sorting in java code. It can look like this:
@Dao
public abstract class MediaItemDao() {
@Query("your query to get the videos, no order by")
protected List<VideoMediaItem> getVideosByAlbumId(int albumId);
@Query("your query to get the photos, no order by")
protected List<PhotosMediaItem> getPhotosByAlbumId(int albumId);
@Transaction
public List<MediaItem> getByAlbumId(int albumId) {
final List<MediaItem> mediaItems = new LinkedList<>();
mediaItems.add(getVideosByAlbumId(albumId));
mediaItems.add(getPhotosByAlbumId(albumId));
Collections.sort(mediaItems, <you can add a comparator here>);
return mediaItems;
}
}
EDIT: How to leverage live data for this option?
As I mentioned, you should use LiveData as the return type of your protected methods so you get notified for underlying changes on those tables. So they should look like this:
protected LiveData<List<VideoMediaItem>> getVideosByAlbumId(int albumId);
protected LiveData<List<PhotosMediaItem>> getPhotosByAlbumId(int albumId);
In order to return a single LiveData to the client, you should zip the outputs of those two methods into a single stream. You can achieve this using a custom MediatorLiveData implementation. It may look like this:
public class ZipLiveData<T1, T2, R> extends MediatorLiveData<R> {
private T1 mLastLeft;
private T2 mLastRight;
private Zipper<T1, T2, R> mZipper;
public static final <T1, T2, R> LiveData<R> create(@NonNull LiveData<T1> left, @NonNull LiveData<T2> right, Zipper<T1, T2, R> zipper) {
final ZipLiveData<T1, T2, R> liveData = new ZipLiveData(zipper);
liveData.addSource(left, value -> {
liveData.mLastLeft = value;
update();
});
liveData.addSource(right, value -> {
liveData.mLastRight = value;
update();
});
return liveData;
}
private ZipLiveData(@NonNull Zipper<T1, T2, R> zipper) {
mZipper = zipper;
}
private update() {
final R result = zipper.zip(mLastLeft, mLastRight);
setValue(result);
}
public interface Zipper<T1, T2, R> {
R zip(T1 left, T2 right);
}
}
Then you just use it in your repository public method like this:
public List<MediaItem> getByAlbumId(int albumId) {
return ZipLiveData.create(
getVideosByAlbumId(albumId),
getPhotosByAlbumId(albumId),
(videos, photos) -> {
final List<MediaItem> mediaItems = new LinkedList<>();
mediaItems.add(videos);
mediaItems.add(photos);
Collections.sort(mediaItems, <you can add a comparator here>);
return mediaItems;
}
}
Option 3
This is applicable only if you have a repository layer in place.
You use two different tables to store VideosMediaItem and PhotosMediaItem. You have a DAO class for each one. You have a repository that depends on the both DAOs and combines the result sets, applying the sorting. It can look like this:
@Dao
public abstract class VideosMediaItemDao {
@Query("your query to get the videos, no order by")
public List<VideoMediaItem> getByAlbumId(int albumId);
}
@Dao
public abstract class PhotosMediaItemDao {
@Query("your query to get the photos, no order by")
public List<PhotosMediaItem> getByAlbymId(int albumId);
}
public interface MediaItemRepository {
public List<MediaItem> getByAlbumId(int albumId);
}
class MediaItemRepositoryImpl {
private final VideosMediaItemDao mVideoDao;
private final PhotosMediaItemDao mPhotoDao;
MediaItemRepositoryImpl(VideosMediaItemDao videoDao, PhotosMediaItemDao photoDao) {
mVideoDao = videoDao;
mPhotoDao = photoDao;
}
@Override
public List<MediaItem> getByAlbumId(int albumId) {
final List<MediaItem> mediaItems = new LinkedList<>();
mediaItems.add(mVideoDao.getByAlbumId(albumId));
mediaItems.add(mPhotoDao.getByAlbumId(albumId));
Collections.sort(mediaItems, <you can add a comparator here>);
return mediaItems;
}
}
I was playing a bit with it and seems found a generic solution to zip multiple LiveData sources together.
import androidx.lifecycle.LiveData
import androidx.lifecycle.MediatorLiveData
class ZipLiveData<S>(private val process: (List<S>) -> List<S>) : MediatorLiveData<List<S>>() {
val map = hashMapOf<String, List<S>>()
fun addSource(source: LiveData<*>) {
addSource(source) {
map[source.toString()] = it as List<S>
value = process(map.values.flatten())
}
}
}
And usage:
@Transaction
fun findItems(albumId: Int): LiveData<List<MediaItem>> {
val liveData = ZipLiveData<MediaItem> { it.sortedBy { item -> item.weight } }
liveData.addSource(getVideosByAlbumId(albumId))
liveData.addSource(getPhotosByAlbumId(albumId))
liveData.addSource(getSoundsByAlbumId(albumId))
return liveData
}
Not sure if it's the most elegant solution though
