I am trying to get unique column values in my loader. My code is like this :-
public Loader onCreateLoader(int id, Bundle args) {
if (mSelectedSpinnerOption == 1) {
return new CursorLoader(this, Suggestion.CONTENT_URI, Suggestion.PROJECTION,
BadBehaviourColumns.PACKAGENAME="DISTINCT", null, BadBehaviourColumns.TIMESTAMP + " ASC ");
}
}
I want all the values with distinct package name values. How do i achieve this using the above code?
I am writing this answer because I faced the exact same issue while querying with CursorLoader and all I got were the answers related to database.query().
There is a simple trick which can be used something on the lines of SQL injection. Instead of using DISTINCT, here GROUP BY has been used which works faster unless you are low on memory (Ref: Huge performance difference when using group by vs distinct).
Following is the query you have to execute to get distinct column1 items from table_name.
SELECT column1 FROM table_name GROUP BY column1;
With this technique the query you end up executing.
SELECT column1 FROM table_name where (column1 NOT NULL) GROUP BY (column1);
Following is the implementation in android.
String selection = "(" + column1 + " NOT NULL) GROUP BY (" + column1 + ")";
CursorLoader c = new CursorLoader(this, uri, projection,
selection, null, null);
Just hacked this out myself, and yes it is very much an SQL injection. However, it does work so long as you manage to get the resulting (generated) SQL to end up correct. Check out this example:
Cursor c_buckets = getContext().getContentResolver().query(baseUri,
new String[]{PhotoContract.ThumbEntry.COLUMN_BUCKET_NAME,
PhotoContract.ThumbEntry.COLUMN_BUCKET_ID + " as _ID"},
"1=1) GROUP BY " + PhotoContract.ThumbEntry.COLUMN_BUCKET_NAME
+ ", " + PhotoContract.ThumbEntry.COLUMN_BUCKET_ID
+ ", ABS(0",
null,
PhotoContract.ThumbEntry.COLUMN_BUCKET_ID
);
The selection field in the query call amounts to the WHERE clause of the resulting query. The "trick" is to add the GROUP BY clause here, after the WHERE clause at the end of the selection. The WHERE clause is enclosed in parentheses, so the trick is to add a closing parenthesis after the WHERE clause (which here is simply 1=1) by adding 1=1) to the selection. Then you can insert your GROUP BY clause. However, since Android will expect all of this to be the selection, it will add a closing parenthesis at the end! To overcome that you need to add something which requires a closing parenthesis at the end. I found it convenient to simply add ABS(0 to the grouping. This has no effect on the grouping, and it solves the syntactical problem, resulting in valid SQL:
SELECT images.bucket_display_name, images.bucket_id as _ID FROM images WHERE (1=1) GROUP BY images.bucket_display_name, images.bucket_id, ABS(0) ORDER BY images.bucket_id
Note I'm adding the required "_id" column using an alias without disturbing the grouping.
UPDATE for Android Q (API 29):
For this to work on API 29 (Android Q), column names must no longer be prefixed with table name. As in, images.bucket_display_name should be just bucket_display_name.
I found the answer myself:-
complete query is something like this-- Select * from table_name where column_name1 in (select distinct column_name1 from table_name) group by column_name1.
So, using the part after where from the above query in place of Selection in the question asked will give all the distinct column name.