CODEWITHSUNDEEP
androidjoinormlite
Matt Rowland
Matt Rowland

Reputation: 379

ORMLite - Find union of results from two tables

I have two database-persisted classes in my Android app using ORMLite -

Contact:

@DatabaseTable(tableName = "contacts")
public class Contact {
    @DatabaseField(id = true, columnName = "_id")
    private int id;
    @DatabaseField
    private String first_name;
    @DatabaseField
    private String last_name;
    @ForeignCollectionField(eager = false)
    ForeignCollection<Phone> phones;
}

and Phone:

@DatabaseTable(tableName = "phones")
public class Phone {
    @DatabaseField(id = true, columnName = "_id")
    private int id;
    @DatabaseField
    private String number;
    @DatabaseField(foreign = true)
    private Contact contact;
}

As you can see, a Contact has many Phones. What I'm trying to do is generate a query, given a CharSequence constraint, to find any Contact whose first_name, last_name, or phone.number matches the constraint.

It's easy enough to get the Contacts who match first_name or last_name:

RuntimeExceptionDao<Contact, Integer> contactsDao = getHelper().getContactsDao();
QueryBuilder<Contact, Integer> contactQb = contactsDao.queryBuilder();
Where contactWhere = contactQb.where();

contactWhere.or(
    contactWhere.like("first_name", "%" + constraint + "%"),
    contactWhere.like("last_name", "%" + constraint + "%")
);

PreparedQuery<Contact> pq = contactQb.prepare();

and it's easy enough to get the Contacts who match with a phone number:

RuntimeExceptionDao<Contact, Integer> contactsDao = getHelper().getContactsDao();
RuntimeExceptionDao<Phone, Integer> phonesDao = getHelper().getPhonesDao();
QueryBuilder<Contact, Integer> contactQb = contactsDao.queryBuilder();
QueryBuilder<Phone, Integer> phoneQb = phonesDao.queryBuilder();

phoneQb.where().like("number", "%" + constraint + "%");
PreparedQuery<Contact> pq = contactQb.join(phoneQb).prepare();

But when I try to combine the two, it seems to give me the intersect of the two data sets in the final cursor (which, as you can imagine, is usually 0 results). Is there some way to get the union of the data sets instead?

I'm aware that ORMLite doesn't support RIGHT JOIN style queries or returning data from a join table into the results, but that's not what I want - all I need is the list of Contacts.

Also note that I'm using a CursorAdapter, so (as far as I'm aware) I can't simply make two requests and then join the resulting Arrays together. The data is destined to be displayed in a ListView.

Example

contacts table

|   id   |  first_name  |  last_name  |
---------------------------------------
|   10   |  Matthew     |  Smith      |
---------------------------------------
|   21   |  John        |  Smith      |
---------------------------------------

phones table

|   id   |  number      | contact_id  |
---------------------------------------
|   99   |  0444444444  |     10      |
---------------------------------------
|  123   |  0444666666  |     21      |
---------------------------------------

A search for "Smith" would return both Contacts. A search for "4444" would return only Matthew Smith, a search for "0666" would return only John Smith, and a search for "044" would return both Contacts.

EDIT - Bonus points if the solution returns only unique results - the other side effect of the way I'm currently doing it is that each result is displayed in the ListView multiple times - once for its name, and again for each Phone it has.

Upvotes: 1

Views: 1314

Answers (1)

Gray
Gray

Reputation: 116888

But when I try to combine the two, it seems to give me the intersect of the two data sets in the final cursor (which, as you can imagine, is usually 0 results). Is there some way to get the union of the data sets instead?

If I am understanding Matt, what you are trying to do is use the same constraint against both the name and the number and show the results that match -- hence the word "union". I guess having a nameConstraint and then a numberConstraint is not an option.

Looks like there is a missing feature here. By default when ORMLite is combining the WHERE statements from the query-builder and the joined query-builder, it currently uses AND. I've just added QueryBuilder.joinOr(...) and innerJoinOr(...) to ORMLite trunk and it will be in version 4.47 that uses OR.

Until this release comes out you will have to use the dao.queryRaw(...) method and do this as a raw query unfortunately.

Upvotes: 1

Related Questions