Search code examples
androidsqliteandroid-contentprovider

selectionArgs in ContentResolver.query(...) can be a subquery?

I'm working with SQLite on Android.
I used ContentProvider to query data from db. And now, I have a problem when try to use subquery via ContentResolver

String selection = "cat_id NOT IN ?"
String[] selectionArgs = new String[]{"(SELECT Categories.id FROM Categories)"}
cursor = mResolver.query(getContentUri(), getListColumns(),
                    selection, selectionArgs, orderBy);

And this is the error:

08-06 10:32:36.070: E/AndroidRuntime(2151): Caused by: android.database.sqlite.SQLiteException: near "?": syntax error (code 1): , while compiling: SELECT * FROM TRANSACTIONS WHERE cat_id NOT IN ? ORDER BY time_created ASC, id ASC`

My question is "Can I use selectionArgs be a Subquery?"
My purpose is "get the list of transactions where cat_id is NOT IN a Category table".
Who can help me?

Solution

  • Don't forget here that ? is actually a placeholder for a value. Which will be binded later by android.

    As such, you would not put a query to be replaced by ?. As the point of using the ? is to ensure that sql code is not injected by user parameters.

    String selection = "cat_id NOT IN ?"
String[] selectionArgs = new String[]{"(SELECT Categories.id FROM Categories)"}
cursor = mResolver.query(getContentUri(), getListColumns(), selection, selectionArgs, orderBy);

    Is equilivant to you writing something like

    String selection = "cat_id NOT IN '(SELECT Categories.id FROM Categories)'"

    Where the query you want run is actually being though of as a value, meaning that NOT IN '(some value)' is not valid sql.

    I suggest that you just remove the ? and replace it with the query you have in your where arguments which will fix it.

    You would use the ? placeholders like this (if you knew what it didn't have to be in).

    String selection = "cat_id NOT IN (?, ?)"
String[] selectionArgs = new String[]{"value1", "value2"}
cursor = mResolver.query(getContentUri(), getListColumns(), selection, selectionArgs, orderBy);

    Edit: Try

    String selection = "cat_id NOT IN (SELECT Categories.id FROM Categories)"
cursor = mResolver.query(getContentUri(), getListColumns(), selection, null, orderBy);