Reapeated columns using setFilterQueryProvider

Question

I have a listview with a problem. I want to implement the classic search with the edittext, i am using the addTextChangedListener with TextWatcher(). The Listview gets the elements from a database so I use cursor and simplecursoradapter so i have to use the setFilterQueryProvider. The problem appears when I write something in the edittext, if I write the name of a product it changes all the names of the elements in the list.So i dont know what to do. Appreciate the help.

here is my java code with the listview:

public class Lista_general extends ListActivity {

SimpleCursorAdapter adapter;
ListView list;
@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.lista_general);


     list = getListView();


    EditText edit =(EditText)findViewById(R.id.edit);


    // open database
    AdminSQLiteOpenHelper dbhelper = new AdminSQLiteOpenHelper(
            getBaseContext());
    SQLiteDatabase db = dbhelper.getReadableDatabase();

    // array for SimpleCursorAdapter
        String columns[] = new String[] { "PRODUCTO._id",  
       "nombre","category","CATEGORIAS._id","categoryid" };


    String orderBy = "category";


          // query database

       Cursor c = db.query("PRODUCTO, CATEGORIAS WHERE CATEGORIAS._id = categoryid  ", 
       columns,null,null, null, null, orderBy);


    c.moveToFirst();

    // array for SimpleCursorAdapter
    String from[] = new String[] { "nombre", "category", };
    //String from[] = new String[] { "nombre", "categoria", };

    int to[] = new int[] { R.id.name, R.id.cate, };

    // Adapter
    adapter = new SimpleCursorAdapter(getBaseContext(),
            R.layout.productos, c, from, to,
            SimpleCursorAdapter.FLAG_REGISTER_CONTENT_OBSERVER);

    setListAdapter(adapter);


    list.setTextFilterEnabled(true);

    //Listener edit text
    edit.addTextChangedListener(new TextWatcher() {

        @Override
        public void onTextChanged(CharSequence s, int start, int before,
                    int count) {

        }

        @Override
        public void beforeTextChanged(CharSequence s, int start, int count,
                int after) {


        }

        @Override
        public void afterTextChanged(Editable s) {

            adapter.getFilter().filter(s.toString());  

        }
    });


    adapter.setFilterQueryProvider(new FilterQueryProvider() {

        @Override
        public Cursor runQuery(CharSequence constraint) {
            // TODO Auto-generated method stub

            AdminSQLiteOpenHelper dbhelper = new AdminSQLiteOpenHelper(
                    getBaseContext());
            SQLiteDatabase db = dbhelper.getReadableDatabase();


             Cursor mCursor = null;
              if (constraint == null  || constraint.length () == 0)  {


              mCursor = db.query("PRODUCTO, CATEGORIAS", new String[] { 
                      "PRODUCTO._id", "nombre","CATEGORIAS._id","category"}, 
              null, null, null, null, null);


              }
              else {



               mCursor = db.query(true,"PRODUCTO, CATEGORIAS", new String[] 
                      {"PRODUCTO._id", "nombre", "category","CATEGORIAS._id"}, 
              "nombre" + " like '%" + constraint + "%'", null,                      
                      null, null, null, null);
              }
              if (mCursor != null) {
               mCursor.moveToFirst();
              }



            return mCursor;
        }
    });
}

Here is a visual of my error:

first my normal list: http://i40.tinypic.com/2111k0p.png

after I wrote: http://i44.tinypic.com/23j04kg.png

Answer 1

It looks like the queries generated in the FilterQueryProvider are not joining the tables properly, so that you end up with every possible combination of PRODUCTO and CATEGORIAS (which are then filtered by PRODUCTO.nombre to give the impression that all the names have changed).

There's also a potential security risk with inserting constraint directly into the query, this opens the door to SQL injection attacks. I'm not sure how serious this is in the context of Android apps, but in for example a PHP web application this would allow anyone to execute any SQL they wished by entering a carefully crafted constraint.

---

From the answers to this question it looks like a rawQuery() call is needed in order to use SQL JOIN so I would change your queries as follows...

For querying with no filter (i.e. in onCreate(); and in runQuery() where there is no constraint):

cursor = db.rawQuery("SELECT PRODUCTO._id, nombre, category, CATEGORIAS._id FROM PRODUCTO INNER JOIN CATEGORIAS ON PRODUCTO.categoryid = CATEGORIAS._id", null);

For querying with a filter:

String[] params = { constraint.toString() };
cursor = db.rawQuery("SELECT PRODUCTO._id, nombre, category, CATEGORIAS._id FROM PRODUCTO INNER JOIN CATEGORIAS ON PRODUCTO.categoryid = CATEGORIAS._id WHERE nombre LIKE ('%' || ? || '%')", params);