Can I perform this Android query with ContentResolver.query()? (LEFT JOIN and CASE)

Question

I am looking to perform the following query (in pseudo-code) on Android:

SELECT C.ID, C.NAME, CASE ISNULL(G.GROUPID,0) = 0 THEN 0 ELSE 1 END INGROUP
FROM CONTACTS C 
LEFT JOIN GROUPMEMBERSHIP G ON G.CONTACTID = C.ID AND G.GROUPID = ?

I am looking to select the ID and Name of ALL contacts in the system address book, via the default Contacts ContentProvider, along with a 0/1 field indicating whether the contact is a member of group ? .

I could of course get all contacts easily enough, then loop through and query the membership separately easy enough in my Adapter class, but I'd imagine performing the two queries as one outer joined query would yield much better performance.

Can I do this with the standard high-level string-projection and ContentResolver.query() method? Or would this kind of query require digging into more direct SQL execution?

Sashenka

Answer 1

Nope, you can't do that kind of queries with the ContentResolver.query() method. You will need to write something like this:

SQLiteDatabase db = YourActivity.getDbHelper().getReadableDatabase();
String query = yourLongQuery;
Cursor c = db.rawQuery(query, null);
YourActivity.startManagingCursor(c);
c.setNotificationUri(YourActivity.getContentResolver(), YourContentProvider.CONTENT_URI);

Maltese

From searching around, i get the impression that you cannot use SQLLiteDatabase for querying other apps... in my case the CONTACTS uri, but only for querying your own datastores. Is this correct? In which case I guess I would be stuck with performing subqueries in my Adapter class when binding my activity. – Sashenka

@eidylon: I didn't know you were querying the Contacts db. Yep, you are right :( – Maltese

Drat! Oh well... onwards and upwards! Thanks! – Sashenka

Bottom line, one would have to be THE content provider and define a special URI that would then build the query special for the complexity. Right? @ediyon - I think you can accept Macarse's answer. – Monatomic

Answer 2

Edit: Okay, so this doesn't actually solve the question asked, because eidylon is tied to an existing ContentProvider as mentioned in their question. However, this does cover how you do a JOIN if you own the ContentProvider source and API. So I'll leave it for those who want to know how to handle that case.

---

This is easy! But unintuitive... :)

query(Uri uri, String[] projection, String selection, 
String[] selectionArgs, String sortOrder)

Okay, so what is URI? Typically, you have one URI per table.

content://com.example.coolapp.contacts serves data out of your CONTACTS table. content://com.example.coolapp.groupmembers serves data out of your GROUPMEMBERSHIP table.

But URI is really just a string. Use it however you like. Make a block of code in your ContentProvider that responds to content://com.example.coolapp.contacts_in_group. Within that block of code in the ContentProvider, you can get raw access to your SQLite DB, unfettered by the limited query() data model. Feel free to use it!

Define your selection fields however you like. They don't have to map to table column names -- map them how you need to, in order to get your parameters in.

Define your projection how you need -- It may contain columns from both tables after the join.

Bing, you're done. Google does this same model internally in their own code -- Go look at the Contacts provider API -- you see "bla.RawContact" and "bla.Contact" and etc as content URIs. Each serves data out of the same table in the DB -- the different URIs just provide different views of that same table!

Thermistor

Thanks for the answer... it is very detailed and informative, so I upvoted; but apparently it was I that was unclear in my Q that I was querying the Android Contacts db, not my own db's. You weren't the only one who missed this. I've updated my wording to be a little clearer about that. Thanks! :) – Sashenka

Sorry, yes. If you're using an existing contactProvider, you're stuck with whatever content URIs and joins they implemented. – Thermistor

so to sum this up, in your ContentProvider, override the "query" method, check for your URI, and do whatever you want with a direct connection to your SQLite db? thanks! – Welcher

@Thermistor Can you please give a sample code of using inner join on the contentResolver ? – Reproach

@Thermistor Please, do you think you could help with this #27257916? – Fabrin

Answer 3

You can't do that because ContentResolver has only one query method:

    query(Uri uri, String[] projection, String selection, 
String[] selectionArgs, String sortOrder)

there's no parameter for tables or FROM clauses.

Vandal