How to sort by first name and last name in search bar - Sqlite

Question

I have an Android application that should replace the Android native contacts.

I want to add the possibility to the user to search a user based on character constraint.

For example:

this is my contacts table:

id  firstName  lastName
1.    Smith      Jean
2.    allen      carr
3.               zetter
4.    john       Stewart
5.    Smith      Allen
6.    Smith      Davey
7.               Smitten
8.    barney     saltzberg

If the user enters the character 's', I want to give him all the contacts statrting with 's' in their first name OR last name, sorted by the first name first and then the last name. From the table before the result I want to get is:

id  firstName  lastName
1.    Smith      Allen
2.    Smith      Davey
3.    Smith      Jean
4.    barney     saltzberg
4.               Smitten
5.    john       Stewart

UPDATE: The problem is when the First name is equals to NULL, the sort is not working and the row is showed before it should. I tried marcin's answer and it's give me the wrong result.

I tried the following:

1. String selection = PeopleDataBase.COLUMN_FIRST_NAME + " LIKE '" + constraint + "%' OR " + PeopleDataBase.COLUMN_LAST_NAME + " LIKE '" + constraint + "%'";

   Cursor cur = db.query(PeopleDataBase.TABLE_PEOPLE, null, selection, null, null, null, null);

I thought to achieve this by two different queries, one for first name and one for last name and then concatenate them to one cursor, but I'm sure there is a better solution.

UPDATE: I also tried to sort in the following way with no success.

   Cursor cur = db.query(PeopleDataBase.TABLE_PEOPLE, null, selection, null, null, null, PeopleDataBase.COLUMN_FIRST_NAME + "," + PeopleDataBase.COLUMN_LAST_NAME);

Do you have a better solution?

Answer 1

how do you display the contacts ? in a listview ?

another idea is to load all entries in a listview and than you can filter the list

you can create a custom Listview and implement Filterable

public class YourContactsListAdapter extends BaseAdapter implements Filterable {
  //some methodes to override 

   @Override
    public Filter getFilter() {
        Filter filter = new Filter() {
            @Override
            protected FilterResults performFiltering(CharSequence c) {}
 @Override
            protected void publishResults(CharSequence charSequence, FilterResults filterResults) {}
}
}

maybe the easiest but not best way.. Hopefully I have not misunderstood the question.

//EDIT:

try cursorAdapter not BaseAdapter here is an example... http://tausiq.wordpress.com/2012/08/22/android-list-view-from-database-with-cursor-adapter/