In GreenDao, build a join query with OR instead of AND

Question

I generated an SQLite DB with GreenDao v2.1.0. Here is its diagram (a tiny piece of it)

A CONTACT can have many phone numbers. I want to make a search query : list all contacts whose GIVEN_NAME or FAMILY_NAME or PHONE.NUMBER contains a specific word.

For example, with these entries, if I use the word "bob" the contact Sponge Bob will be returned. If I use the word "222", the contact Patrick Star will be returned.

Since, two tables are involved in the query, I resorted to the JOIN solution with this piece of code :

    QueryBuilder<Contact> qb = getContactDao(context).queryBuilder();
    qb.whereOr(ContactDao.Properties.Given_name.like("%" + word + "%"),
               ContactDao.Properties.Family_name.like("%" + word + "%"));
    qb.join(Phone.class, PhoneDao.Properties.Contact_id)
      .where(PhoneDao.Properties.Number.like("%" + word + "%"));
    List<Contact> contacts = qb.list();

This generates the following SQL :

SELECT T."_id", T."GIVEN_NAME", T."FAMILY_NAME"
FROM "CONTACT" T  
JOIN PHONE J1 
ON T."_id"=J1."CONTACT_ID" 
WHERE (T."GIVEN_NAME" LIKE ? OR T."FAMILY_NAME" LIKE ?) AND J1."NUMBER" LIKE ?    
COLLATE LOCALIZED ASC

The 5th line points out the problem : the "AND" connector. I am desperately trying to replace it with an "OR".

Am I missing something ? Shall I leave the JOIN solution ? Thanks :)

Answer 1

I have the same problem. It seems that greendao is currently not able to do that. I am resorting to using queryRaw() instead.