Using ContactsContract, query matching name or email

Question

Is it possible to do one query to ContactsContract to get a set of contacts which match either the display name or a phone number or an email address?

This is essentially a join of the Contacts and Data "tables" (in quotes because they only look like tables through the interface but may not be).

In other words, I'd like a where clause something like (simplifying the syntax a bit)

where Contacts.DISPLAY_NAME like "%?%" 
   or (Data.MIMETYPE = Phone.CONTENT_ITEMTYPE
       and CommonDataKinds.Phone.NUMBER like "%?%")
   or (Data.MIMETYPE = Email.CONTENT_ITEMTYPE
       and CommonDAtaKinds.Email.ADDRESS like "%?%")

and the tables are joined like

Data.RAW_CONTACT_ID = RawContacts.ID and RawContacts.CONTACT_ID = Contacts.ID

I could do this as separate queries without the join, but then ordering the results becomes difficult. I'd like them ordered by display name.

score 1 · Answer 1 · answered May 22 '18 at 13:11

If you only want to match name/email/phone then you can do:

WHERE Data.MIMETYPE IN (StructuredName.CONTENT_ITEM_TYPE, Phone.CONTENT_ITEM_TYPE, Email.CONTENT_ITEM_TYPE) 
AND Data.DATA1 LIKE "%?%"

If you're ok with extending the possible match fields, then you can simplify the query to:

WHERE Data.DATA1 LIKE "%?%"

This will match CommonDataKinds.StructuredName.DISPLAY_NAME, CommonDataKinds.Email.ADDRESS, CommonDataKinds.Phone.NUMBER, and more.

score 0 · Accepted Answer · answered May 17 '18 at 01:22

The answer was stairing me in the face. The Data table is already a join (or looks like one, maybe it is a denormalized table internally). Thus, all I need to do is query for all three matches directly:

where Data.DISPLAY_NAME like "%?%" 
   or (Data.MIMETYPE = Phone.CONTENT_ITEMTYPE
       and CommonDataKinds.Phone.NUMBER like "%?%")
   or (Data.MIMETYPE = Email.CONTENT_ITEMTYPE
       and CommonDAtaKinds.Email.ADDRESS like "%?%")

Note the Data.DISPLAY_NAME, above.

Using ContactsContract, query matching name or email

2 Answers2