Android and SQLite : is there no good way to join queries with the ContactsContract API

Question

I feel as though I may be missing something in the ContactsContract API. In my application I have several SQLite tables with references to Contacts or Groups (from the ContactsContract API). I did this rather than reinvent the wheel with my own contacts and groups tables.

Making the queries work together is turning out to be a nightmare, however. Suppose I want to perform an operation on my SQL table using all of the contacts from a particular group. I'm having to query ContactsContract to get the contact_id's of members of a group, join those contact_id's into a string, and then put that string into a separate query. (Or have SQL queries in a loop.)

This is such bad SQL that I feel I must be doing something wrong, but I can't find references to any other way to do it. I'm on the verge of just maintaining my own list of contacts. Any ideas?

score 3 · Accepted Answer · answered Jun 16 '12 at 07:25

Nope, you aren't on the verge of anything wrong. The Contacts ContentProvider (and pretty much every ContentProvider I've seen) contradicts conventional SQL wisdom, oh well.

If you want to get data from multiple types of ContactsContracts.CommonDataKinds, you need to detect the mime-type of the row you're in and determine what kind of entity it is (or make an extra query, my preferred solution is to do it all in one query...)

Hold your breath, here's an example of some code I wrote to do such a thing. You should be able to see how the different rows can be different kinds of entities and as a result of that their generic columns can hold different kinds of data, which is why we use CommonDataKinds.* classes to reference them contextually:

public class ContactsHelper {


    private static String[] PROJECTION = {
        Data.CONTACT_ID,
        Data.MIMETYPE,
        StructuredName.GIVEN_NAME,
        StructuredName.FAMILY_NAME,
        StructuredName.DISPLAY_NAME,
        StructuredName.MIDDLE_NAME,
        Email.ADDRESS
    };

    public static void readContacts(Context context) {
        ContentResolver resolver = context.getContentResolver();
        Uri contactsUri = ContactsContract.Data.CONTENT_URI;

        SQLiteDatabase conn = DatabaseHelper.openDatabase(context);

        Cursor cursor = resolver.query(contactsUri, PROJECTION, null, null, null);

        int idxContactId = cursor.getColumnIndex(Data.CONTACT_ID);
        int idxMimeType = cursor.getColumnIndex(Data.MIMETYPE);
        int idxGivenName = cursor.getColumnIndex(StructuredName.GIVEN_NAME);
        int idxFamilyName = cursor.getColumnIndex(StructuredName.FAMILY_NAME);
        int idxDisplayName = cursor.getColumnIndex(StructuredName.DISPLAY_NAME);
        int idxMiddleName = cursor.getColumnIndex(StructuredName.MIDDLE_NAME);
        int idxEmail = cursor.getColumnIndex(Email.ADDRESS);

        for (cursor.moveToFirst(); ! cursor.isAfterLast(); cursor.moveToNext()) {
            String mimeType = cursor.getString(idxMimeType);

            Integer contactId = cursor.getInt(idxContactId);

            if (StructuredName.CONTENT_ITEM_TYPE.equals(mimeType)) {
                String firstName = cursor.getString(idxGivenName);
                String middleName = cursor.getString(idxMiddleName);
                String lastName = cursor.getString(idxFamilyName);
                String displayName = cursor.getString(idxDisplayName);

                            Log.d(TAG, all the values ^^^);

            }


            if (Email.CONTENT_ITEM_TYPE.equals(mimeType)) {
                String emailName = cursor.getString(idxEmail);
                Log.d(TAG, all the values ^^^);
            }
        }

        DatabaseHelper.closeDatabase(conn);
        cursor.close();
    }   
}

Yes this completely serious solution does use string comparisons. If there is a better way, please let me know!

Wow, how strange of that. Confession: before waiting for this answer I gave made my own contacts tables. My functions are shorter and more elegant; helper classes I'd written have been eliminated; my code looks a lot better. I'm sure it's more efficient as well. Thanks for your help and your answer. — adam.baker, Jun 16 '12 at 07:51
I would do that as well.. Just observe the contacts and import them into your own DB if you have a complicated problem to solve so you can actually use SQL! — Thomas Dignan, Jun 16 '12 at 18:35
I guess One can improve your code, but still its very clear and very helpful. Thanks a lot! — gor, Jun 23 '14 at 09:13

Android and SQLite : is there no good way to join queries with the ContactsContract API

1 Answers1