README.md

Content Resolver SQL

Allows to write SQL statements instead of using contentResolver.query(...). The library add an extension function to ContentResolver named querySql(query: String, selectionArgs: Array<String>? = null)

Limitations

• When using LIMIT keyword, you need to specify also ORDER BY.

• When using GROUP BY keyword, you need to specify also WHERE.

• You can use WHERE 1 GROUP BY if you haven’t a WHERE condition
• JOIN are not supported by android content provider itself.

Getting started

Step 1. Add the JitPack repository to your build file Add it in your root build.gradle at the end of repositories:

allprojects {
    repositories {
        ...
        maven { url 'https://jitpack.io' }
    }
}

Step 2. Add the dependency

implementation 'com.github.ologe:android-content-resolver-SQL:1.2.2'

Example 1

Get all songs

val query = """ 
    SELECT *
    FROM ${Media.EXTERNAL_CONTENT_URI}
""" 
contentResolver.querySql(query)

instead of

contentResolver.query(Media.EXTERNAL_CONTENT_URI, null, null, null, null)

Example 2

Get first 10 artists offsetted by 2, that have at least 5 tracks and 2 albums, excluding the podcast, ordered by artist desc

val query = """
    SELECT distinct $ARTIST_ID, $ARTIST, count(*) as songs, count(distinct $ALBUM_ID) as albums
    FROM ${Media.EXTERNAL_CONTENT_URI}
    WHERE $IS_PODCAST = 0
    GROUP BY $ARTIST_ID
    HAVING songs >= 5 AND albums >= 2
    ORDER BY $ARTIST_KEY DESC
    LIMIT 10
    OFFSET 2
"""
contentResolver.querySql(query)

instead of

contentResolver.query(
    uri = Media.EXTERNAL_CONTENT_URI,
    projection = arrayOf(
        "distinct $ARTIST_ID", 
        ARTIST, 
        "count(*) as songs", 
        "count(distinct $ALBUM_ID) as albums"
    ),
    selection = "$IS_PODCAST = 0 ) GROUP BY $ARTIST_ID HAVING (songs >= 5 AND albums >= 2",
    selectionArgs = null,
    sortOrder = "$ARTIST_KEY DESC LIMIT 20 OFFSET 2"
)