// *****************************
// function
// *****************************

public static List<DBMedia> getMedia()
{
    ArrayList<DBMedia> medias = new ArrayList<>();

    SquidCursor<MediaView> cursor = MainApp.getDB().query(MediaView.class, Query.select(MediaView.PROPERTIES).from(MediaView.SUBQUERY));
    cursor.moveToFirst();
    MediaView view = new MediaView();
    while (!cursor.isAfterLast())
    {
        view.readPropertiesFromCursor(cursor);
        medias.add(MediaView.getMedia(view));
    }
    cursor.close();

    return medias;
}

// *****************************
// my ViewModel
// *****************************

@ViewModelSpec(className="MediaView", viewName="mediaView", isSubquery = true)
public class TESTModelSpec
{
	// ------------------
	// declare all tables
	// ------------------

	@Constants
	public static class Const
	{
		public static final Table TABLE_FOLDER = DBFolder.TABLE.as("F1");
		public static final Table TABLE_FOLDER_TIMELINE = DBFolder.TABLE.as("F2");
		public static final Table TABLE_FOLDER_LOCATION = DBFolder.TABLE.as("F3");
		public static final Table TABLE_FOLDER_TAG = DBFolder.TABLE.as("F4");
	}

	// ------------------
	// declare all fields necessary for queries
	// ------------------

	public static final Property.LongProperty ID_FOLDER = Const.TABLE_FOLDER.qualifyField(DBFolder.ROWID);
	public static final Property.LongProperty ID_FOLDER_TIMELINE = Const.TABLE_FOLDER_TIMELINE.qualifyField(DBFolder.ROWID);
	public static final Property.LongProperty ID_FOLDER_LOCATION = Const.TABLE_FOLDER_LOCATION.qualifyField(DBFolder.ROWID);
	public static final Property.LongProperty ID_FOLDER_TAG = Const.TABLE_FOLDER_TAG.qualifyField(DBFolder.ROWID);

	// ------------------
	// declare all other fields of the tables so that the models can be fully created
	// ------------------

	public static final Property.IntegerProperty FOLDER_FIELD_1 = Const.TABLE_FOLDER.qualifyField(DBFolder.INTERNAL_FOLDER_TYPE);
	public static final Property.IntegerProperty FOLDER_FIELD_1_TIMELINE = Const.TABLE_FOLDER_TIMELINE.qualifyField(DBFolder.INTERNAL_FOLDER_TYPE);
	public static final Property.IntegerProperty FOLDER_FIELD_1_LOCATION = Const.TABLE_FOLDER_LOCATION.qualifyField(DBFolder.INTERNAL_FOLDER_TYPE);
	public static final Property.IntegerProperty FOLDER_FIELD_1_TAG = Const.TABLE_FOLDER_TAG.qualifyField(DBFolder.INTERNAL_FOLDER_TYPE);

	public static final Property.IntegerProperty FOLDER_FIELD_2 = Const.TABLE_FOLDER.qualifyField(DBFolder.INTERNAL_DATA_SOURCE);
	public static final Property.IntegerProperty FOLDER_FIELD_2_TIMELINE = Const.TABLE_FOLDER_TIMELINE.qualifyField(DBFolder.INTERNAL_DATA_SOURCE);
	public static final Property.IntegerProperty FOLDER_FIELD_2_LOCATION = Const.TABLE_FOLDER_LOCATION.qualifyField(DBFolder.INTERNAL_DATA_SOURCE);
	public static final Property.IntegerProperty FOLDER_FIELD_2_TAG = Const.TABLE_FOLDER_TAG.qualifyField(DBFolder.INTERNAL_DATA_SOURCE);

	public static final Property.StringProperty FOLDER_FIELD_3 = Const.TABLE_FOLDER.qualifyField(DBFolder.DATA);
	public static final Property.StringProperty FOLDER_FIELD_3_TIMELINE = Const.TABLE_FOLDER_TIMELINE.qualifyField(DBFolder.DATA);
	public static final Property.StringProperty FOLDER_FIELD_3_LOCATION = Const.TABLE_FOLDER_LOCATION.qualifyField(DBFolder.DATA);
	public static final Property.StringProperty FOLDER_FIELD_3_TAG = Const.TABLE_FOLDER_TAG.qualifyField(DBFolder.DATA);

	public static final Property.StringProperty FOLDER_FIELD_4 = Const.TABLE_FOLDER.qualifyField(DBFolder.NAME);
	public static final Property.StringProperty FOLDER_FIELD_4_TIMELINE = Const.TABLE_FOLDER_TIMELINE.qualifyField(DBFolder.NAME);
	public static final Property.StringProperty FOLDER_FIELD_4_LOCATION = Const.TABLE_FOLDER_LOCATION.qualifyField(DBFolder.NAME);
	public static final Property.StringProperty FOLDER_FIELD_4_TAG = Const.TABLE_FOLDER_TAG.qualifyField(DBFolder.NAME);

	// ------------------
	// Query
	// ------------------

	@ViewQuery
	public static final Query QUERY = createQuery();

	private static Query createQuery()
	{
		Query query = Query.select().from(DBMedia.TABLE)
				.leftJoin(Const.TABLE_FOLDER, DBMedia.FK_FOLDER.eq(Const.TABLE_FOLDER.qualifyField(DBFolder.ROWID)))
				.leftJoin(Const.TABLE_FOLDER_TIMELINE, DBMedia.FK_FOLDER_DATE.eq(Const.TABLE_FOLDER_TIMELINE.qualifyField(DBFolder.ROWID)))
				.leftJoin(Const.TABLE_FOLDER_LOCATION, DBMedia.FK_FOLDER_LOCATION.eq(Const.TABLE_FOLDER_LOCATION.qualifyField(DBFolder.ROWID)))
				.leftJoin(Const.TABLE_FOLDER_TAG, DBMedia.FK_FOLDER_TAG.eq(Const.TABLE_FOLDER_TAG.qualifyField(DBFolder.ROWID)));

		L.d(TESTModelSpec.class, "SQL: " + query.toRawSql(VersionCode.LATEST));

		return query;
	}

	// ------------------
	// Helper functions
	// ------------------

	public static DBMedia getMedia(MediaView modelView)
	{
		DBMedia media = modelView.mapToModel(new DBMedia(), DBMedia.TABLE);

		DBFolder folder = modelView.mapToModel(new DBFolder(), Const.TABLE_FOLDER);
		DBFolder folderDate = modelView.mapToModel(new DBFolder(), Const.TABLE_FOLDER_TIMELINE);
		DBFolder folderLocation = modelView.mapToModel(new DBFolder(), Const.TABLE_FOLDER_LOCATION);
		DBFolder folderTag = modelView.mapToModel(new DBFolder(), Const.TABLE_FOLDER_TAG);

		media.initFolders(folder, folderTag, folderDate, folderLocation);

		return media;
	}
}