I am attempting to implement a filter on a list view in my application. At the moment I have the a listview that is populated by a cursor that receives the result of a query (this works fine). I have a floating button and when clicked brings up a dialog box with possible filter options for the user to enter.
The options are in the form of three spinners. When the user selects an item from each of the spinners the results are sent to one of three different queries. These queries are what I am using to do the filter and contain a different where clause in each of them. However if nothing is select the variable value is set to 0 or ""
I am running into two problems, the first is that the for loop to check if certain filters have been selected or not (if they have been selected this determines what filter query to use) it seems to be only checking the first for even though I havnt changed values in some of the spinner and the second is in the SQL show below.
I also know I should be using something better then a raw query but I have a kind of complex query that I know works with a raw query so in no rush to change it just yet.
Any help would be great thanks
Spinners
final Spinner heat = (Spinner) dialog.findViewById(R.id.heatSpinner);
heat.setOnItemSelectedListener(new AdapterView.OnItemSelectedListener() {
@Override
public void onItemSelected(AdapterView<?> parentView, View selectedItemView, int position, long id) {
String heat2 = heat.getSelectedItem().toString();
possibleRecipes.finalHeat = Integer.parseInt(heat2);
}
@Override
public void onNothingSelected(AdapterView<?> parentView) {
possibleRecipes.finalHeat = 0;
}
});
final Spinner dietary = (Spinner) dialog.findViewById(R.id.dietarySpinner);
dietary.setOnItemSelectedListener(new AdapterView.OnItemSelectedListener() {
@Override
public void onItemSelected(AdapterView<?> parentView, View selectedItemView, int position, long id) {
possibleRecipes.finalDietary = dietary.getSelectedItem().toString();
}
@Override
public void onNothingSelected(AdapterView<?> parentView) {
possibleRecipes.finalDietary = "";
}
});
final Spinner time = (Spinner) dialog.findViewById(R.id.timeSpinner);
time.setOnItemSelectedListener(new AdapterView.OnItemSelectedListener() {
@Override
public void onItemSelected(AdapterView<?> parentView, View selectedItemView, int position, long id) {
String time2 = time.getSelectedItem().toString();
possibleRecipes.finalTime = Integer.parseInt(time2);
}
@Override
public void onNothingSelected(AdapterView<?> parentView) {
possibleRecipes.finalTime = 0;
}
});
For Loop to check spinner info and run appropriate queries
Button dialogButtonOk = (Button) dialog.findViewById(R.id.dialogButtonOK);
// if button is clicked, close the custom ingredient_dialog
dialogButtonOk.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
if(possibleRecipes.finalHeat != 0 && possibleRecipes.finalTime != 0 && possibleRecipes.finalDietary !="")
{
//full filter search
possibleRecipes.filterCursor = adapter.fullFilter(finalHeat, finalDietary, finalTime);
String[] columns = new String[] {adapter.KEY_RECIPE_NAME, adapter.KEY_ID};
int[] to = new int[] {R.id.recipeName};
SimpleCursorAdapter myCursorAdapter = new SimpleCursorAdapter(getApplicationContext(),R.layout.row1, possibleRecipes.filterCursor, columns, to, 0);
ListView recipeList = (ListView) findViewById(R.id.possibleRecipeList);
recipeList.setAdapter(myCursorAdapter);
}
if(possibleRecipes.finalHeat == 0 && possibleRecipes.finalTime != 0 && possibleRecipes.finalDietary !="")
{
//filter for final time and dietary
possibleRecipes.timeAnddietaryFilterCursor = adapter.timeAnddietaryFilter(finalTime, finalDietary);
String[] columns = new String[] {adapter.KEY_RECIPE_NAME, adapter.KEY_ID};
int[] to = new int[] {R.id.recipeName};
SimpleCursorAdapter myCursorAdapter = new SimpleCursorAdapter(getApplicationContext(),R.layout.row1, possibleRecipes.timeAnddietaryFilterCursor, columns, to, 0);
ListView recipeList = (ListView) findViewById(R.id.possibleRecipeList);
recipeList.setAdapter(myCursorAdapter);
}
if(possibleRecipes.finalHeat != 0 && possibleRecipes.finalTime == 0 && possibleRecipes.finalDietary !="")
{
//filter for heat and dietary
possibleRecipes.heatAnddietaryFilterCursor = adapter.heatAnddietaryFilter(finalHeat, finalDietary);
String[] columns = new String[] {adapter.KEY_RECIPE_NAME, adapter.KEY_ID};
int[] to = new int[] {R.id.recipeName};
SimpleCursorAdapter myCursorAdapter = new SimpleCursorAdapter(getApplicationContext(),R.layout.row1, possibleRecipes.heatAnddietaryFilterCursor, columns, to, 0);
ListView recipeList = (ListView) findViewById(R.id.possibleRecipeList);
recipeList.setAdapter(myCursorAdapter);
}
if(possibleRecipes.finalHeat != 0 && possibleRecipes.finalTime != 0 && possibleRecipes.finalDietary.length()>1)
{
//filter for heat and time
possibleRecipes.heatAndtimeFilterCursor = adapter.heatAndtimeFilter(finalHeat, finalTime);
String[] columns = new String[] {adapter.KEY_RECIPE_NAME, adapter.KEY_ID};
int[] to = new int[] {R.id.recipeName};
SimpleCursorAdapter myCursorAdapter = new SimpleCursorAdapter(getApplicationContext(),R.layout.row1, possibleRecipes.heatAndtimeFilterCursor, columns, to, 0);
ListView recipeList = (ListView) findViewById(R.id.possibleRecipeList);
recipeList.setAdapter(myCursorAdapter);
}
else
{
String[] columns = new String[] {adapter.KEY_RECIPE_NAME, adapter.KEY_ID};
int[] to = new int[] {R.id.recipeName};
SimpleCursorAdapter myCursorAdapter = new SimpleCursorAdapter(getApplicationContext(),R.layout.row1, possibleRecipes.cursor, columns, to, 0);
ListView recipeList = (ListView) findViewById(R.id.possibleRecipeList);
recipeList.setAdapter(myCursorAdapter);
}
dialog.dismiss();
}
});
dialog.show();
Queries for filters
public Cursor fullFilter(int finalHeat, String finalDietary, int finalTime)
{
return db.rawQuery("select recipe_name, _id from recipes where recipes.recipe_code in " +
"(select ingredients.recipe_code from ingredients inner join kitchen on " +
"kitchen.ingredient_name = ingredients.ingredient_name) where recipes.heat = "+finalHeat+" and recipes.dietary='"+finalDietary+"' and recipes.time = "+ finalTime, null);
}
public Cursor timeAnddietaryFilter(int finalTime, String finalDietary)
{
return db.rawQuery("select recipe_name, _id from recipes where recipes.recipe_code in " +
"(select ingredients.recipe_code from ingredients inner join kitchen on " +
"kitchen.ingredient_name = ingredients.ingredient_name) where recipes.dietary='"+finalDietary+"' and recipes.time = "+ finalTime, null);
}
public Cursor heatAnddietaryFilter(int finalHeat, String finalDietary)
{
return db.rawQuery("select recipe_name, _id from recipes where recipes.recipe_code in " +
"(select ingredients.recipe_code from ingredients inner join kitchen on " +
"kitchen.ingredient_name = ingredients.ingredient_name) where recipes.heat = "+finalHeat+" and recipes.dietary='"+finalDietary, null);
}
public Cursor heatAndtimeFilter(int finalHeat, int finalTime)
{
return db.rawQuery("select recipe_name, _id from recipes where recipes.recipe_code in " +
"(select ingredients.recipe_code from ingredients inner join kitchen on " +
"kitchen.ingredient_name = ingredients.ingredient_name) where recipes.heat = "+finalHeat+" and recipes.time = "+ finalTime, null);
}
Error
02-01 19:48:15.305 26534-26534/com.example.rory.prototypev2 E/AndroidRuntime: Process: com.example.rory.prototypev2, PID: 26534
02-01 19:48:15.305 26534-26534/com.example.rory.prototypev2 E/AndroidRuntime: android.database.sqlite.SQLiteException: near "where": syntax error (code 1): , while compiling: select recipe_name, _id from recipes where recipes.recipe_code in (select ingredients.recipe_code from ingredients inner join kitchen on kitchen.ingredient_name = ingredients.ingredient_name) where recipes.heat = 1 and recipes.dietary='Normal' and recipes.time = 15
02-01 19:48:15.305 26534-26534/com.example.rory.prototypev2 E/AndroidRuntime: at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
02-01 19:48:15.305 26534-26534/com.example.rory.prototypev2 E/AndroidRuntime: at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:887)
02-01 19:48:15.305 26534-26534/com.example.rory.prototypev2 E/AndroidRuntime: at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:498)
02-01 19:48:15.305 26534-26534/com.example.rory.prototypev2 E/AndroidRuntime: at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
02-01 19:48:15.305 26534-26534/com.example.rory.prototypev2 E/AndroidRuntime: at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
02-01 19:48:15.305 26534-26534/com.example.rory.prototypev2 E/AndroidRuntime: at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
02-01 19:48:15.305 26534-26534/com.example.rory.prototypev2 E/AndroidRuntime: at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
02-01 19:48:15.305 26534-26534/com.example.rory.prototypev2 E/AndroidRuntime: at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1316)
02-01 19:48:15.305 26534-26534/com.example.rory.prototypev2 E/AndroidRuntime: at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1255)
02-01 19:48:15.305 26534-26534/com.example.rory.prototypev2 E/AndroidRuntime: at com.example.rory.prototypev2.DBMain.fullFilter(DBMain.java:224)
02-01 19:48:15.305 26534-26534/com.example.rory.prototypev2 E/AndroidRuntime: at com.example.rory.prototypev2.possibleRecipes$2$4.onClick(possibleRecipes.java:139)
Aucun commentaire:
Enregistrer un commentaire