I generated an SQLite DB with GreenDao. 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") 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 :)
Aucun commentaire:
Enregistrer un commentaire