21

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?

eidylon
  • 7,068
  • 20
  • 75
  • 118

3 Answers3

42

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!

jcwenger
  • 11,383
  • 1
  • 53
  • 65
  • 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! :) – eidylon Jul 03 '11 at 02:19
  • 1
    Sorry, yes. If you're using an existing contactProvider, you're stuck with whatever content URIs and joins they implemented. – jcwenger Feb 10 '12 at 14:10
  • 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! – Brad Parks May 10 '13 at 20:14
  • @jcwenger Can you please give a sample code of using inner join on the contentResolver ? – android developer Jan 23 '14 at 15:53
  • @jcwenger Please, do you think you could help with this http://stackoverflow.com/questions/27257416/android-how-can-i-pass-data-related-to-two-tables-to-a-the-insert-method-of-a? – eddy Dec 03 '14 at 03:59
8

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);
Macarse
  • 91,829
  • 44
  • 175
  • 230
  • 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. – eidylon Jun 27 '11 at 17:10
  • @eidylon: I didn't know you were querying the Contacts db. Yep, you are right :( – Macarse Jun 27 '11 at 17:45
  • Drat! Oh well... onwards and upwards! Thanks! – eidylon Jun 27 '11 at 20:58
  • 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. – mobibob Jun 29 '11 at 23:38
0

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.

West_Link
  • 115
  • 2