CODEWITHSUNDEEP
androidsqlitecursor
333Matt
333Matt

Reputation: 1188

Removing rows from an Android SQLite Cursor

I query and get a result set back, but I need to do some calculations that are impossible in the SQLite WHERE clause in order to determine what shows up in the ListView. How can I remove certain rows from the cursor? I know it is the same question as this Filter rows from Cursor so they don't show up in ListView but that answer does not help. Can an example be provided if there isn't a simpler way to do this?

Upvotes: 3

Views: 7526

Answers (4)

J. Polfer
J. Polfer

Reputation: 12481

I need to do some calculations that are impossible in the SQLite WHERE clause

I find this very hard to believe; my experience has been that SQL will let you query for just about anything you'd ever need (with the exception of heirarchical or recursive queries in SQLite's case). If there's some function you need that isn't supported, you can add it easily with sqlite_create_function() and use it in your app. Or perhaps a creative use of the SELECT clause can do what you are looking for.

Can you explain what these impossible calculations are?

EDIT: Nevermind, checking out this webpage reveals that the sqlite_create_function() adapter is all closed up by the Android SQLite wrapper. That's annoying.

Upvotes: 0

Graham Borland
Graham Borland

Reputation: 60681

It might work to simply retain all the rows in the Cursor, but then use a custom adapter to hide the unwanted rows at display time. For example, if you extend CursorAdapter, then you might have something like this in your bindView implementation:

View v = view.findViewById(R.id.my_list_entry);
boolean keepThisRow = .......; // do my calculations
v.setVisibility(keepThisRow ? View.VISIBLE : View.GONE);

Upvotes: 4

333Matt
333Matt

Reputation: 1188

There should be a better way to do this, but what I ended up doing is storing the ID of each row I wanted in a string ArrayList, and then requerying where _id IN arraListOfIds.toString(), replacing the square brackets with parentheses to fit SQL syntax.

// Get all of the rows from the database        
mTasksCursor = mDbHelper.fetchAllTasks();

ArrayList<String> activeTaskIDs = new ArrayList<String>();

// calculate which ones belong
// .....

if (!hasCompleted)             
                activeTaskIDs.add(mTasksCursor.getString(TaskerDBadapter.INDEX_ID));

// requery on my list of IDs
mTasksCursor = mDbHelper.fetchActiveTasks(activeTaskIDs);

public Cursor fetchActiveTasks(ArrayList<String> activeTaskIDs)
    {
        String inClause = activeTaskIDs.toString();
        inClause = inClause.replace('[', '(');
        inClause = inClause.replace(']', ')');

        Cursor mCursor = mDb.query(true, DATABASE_TABLE, columnStringArray(), 
                KEY_ROWID + " IN " + inClause, 
                null, null, null, null, null);

        if (mCursor != null) { mCursor.moveToFirst(); }

        return mCursor;
    }

Upvotes: 3

Bill Mote
Bill Mote

Reputation: 12823

ContentResolver cr = getContentResolver();
        Cursor groupCur = cr.query(
                Groups.CONTENT_URI, // what table/content
                new String [] {Groups._ID, Groups.NAME},    // what columns
                "Groups.NAME NOT LIKE + 'System Group:%'", // where clause(s)
                null, // ???
                Groups.NAME + " ASC" // sort order
        );

The "What Columns" piece above is where you can tell the cursor which rows to return. Using "null" returns them all.

Upvotes: 0

Related Questions