How to combine data from two database tables into one listView

Question

I am quite confused about how to combine two separate database queries into one listView.

Currently, my listView is populated by the following adapter which queries the damaged component table in my database and provides a list of damaged components for a certain location:

private class MyListAdapter extends ResourceCursorAdapter { 

    // In your ListActivity class, create a new inner class that extends ResourceCursorAdapter. 
    //This inner class is the custom CursorAdapter we will use to manage how data is bound to a list item:

    public MyListAdapter(Context context, Cursor cursor) { 
        super(context, R.layout.row_location, cursor);
    } 

    @Override
    public void bindView(View view, Context context, Cursor cursor) { 
        TextView text_first_line = (TextView) view.findViewById(R.id.location_row_item_main_text);
        TextView text_second_line = (TextView) view.findViewById(R.id.location_row_item_secondary_text);
        ImageView flagIcon = (ImageView) view.findViewById(R.id.flagIcon);

        String row_text_component = cursor.getString(cursor.getColumnIndex(RMDbAdapter.COMPONENT));
        String row_text_position = ", Position " + cursor.getString(cursor.getColumnIndex(RMDbAdapter.POSITION));
        if(row_text_position.equals(", Position Not Applicable")){
            row_text_position = "";
        }
        String row_text_action = " - " + cursor.getString(cursor.getColumnIndex(RMDbAdapter.ACTION_REQUIRED));

        text_first_line.setText(row_text_component + row_text_position + row_text_action);
        text_second_line.setText("Dexion Speedlock, S Duty, 3000mm");

        String risk = cursor.getString(cursor.getColumnIndex(RMDbAdapter.RISK));
        if (risk.equals("Red Risk")){
            flagIcon.setImageResource(R.drawable.red_flag);
        }
        else if (risk.equals("Green Risk")){
            flagIcon.setImageResource(R.drawable.green_flag);
        }
        else if (risk.equals("No Risk")){
            flagIcon.setImageResource(R.drawable.note);
        }

    }
}

This is triggered when I call the following when the activity is started:

private void setAdapter(){

    // Get a Cursor for the list items

    Cursor listComponentCursor = rmDbHelper.fetchDamagedComponentsForLocation(locationId);
    componentCursorSize = listComponentCursor.getCount();
    startManagingCursor(listComponentCursor); 
    // set the custom list adapter     
    setListAdapter(new MyListAdapter(this, listComponentCursor));

}

So I would also like to create a second query on a separate table (this time issues table) and add this to the listView under the list of damaged components.

Reading this Listview from multiple tables?, I believe that I should use a Join or Merge cursor. However, from what I have researched I still have no idea how to integrate either concept into my code.

Can anyone point me in the right direction please?

Answer 1

Although you don't show it, I'm going to assume you overrode the newView method of the cursor adapter. A MergeCursor will just stack the results of one query on top of the next. A CursorJoiner will pretty much put the results side by side. It sounds like you want a MergeCursor.

If you want to just concatenate the results of a second query to the first, perform both queries and create a MergeCursor. If you need them to have different layouts, or they have different column names, you will need to override the getItemViewType method in the adapter. Then, in your newView method, you can inflate different views based on the type. In bindView you need to check the type and apply the correct values to the correct views.

A couple of random tips for your code:

1) use the ViewHolder pattern, it's way faster.

2) it's always better to do "literal string".equals(variable) instead of variable.equals("literal string") because a literal string cannot throw a NullPointerException