androidjoinadapterandroid-cursorandroid-cursoradapter

Joining multiple records in a CursorAdapter

I'm trying to build a ListView that represents a list a recipes. I get the concept of using a SimpleCursorAdapter to accomplish that.

However, in addition to showing the recipe image & title for each item, I'd like to show a comma-separated string of ingredients associated with the recipe. Given that a cursor is only for one table (or view), I'm not quite sure how to approach this.

Relevant tables/columns are below:

recipes (
  _id integer primary key,
  name text not null
)

ingredients (
  _id integer primary key,
  name text not null
)

recipes_ingredients (
  _id integer primary key,
  recipe_id integer not null,
  ingredient_id integer not null
)

Edit: The first two comments on this question lead me to believe there's not enough information about why I think a simple JOIN won't suffice. Given the table structure, a recipe can have multiple ingredients, not just one. That means if I try to perform a join for a single recipe, I'll end up with something like the following:

SELECT r.name AS recipe, i.name AS ingredient
FROM recipes r
INNER JOIN recipes_ingredients ri ON ri.recipe_id=r._id
INNER JOIN ingredients i ON ri.ingredient_id=i._id;

 recipe           | ingredient
------------------+---------------------
 Chocolate Milk   | milk
 Chocolate Milk   | chocolate syrup
 Chicken Parmesan | chicken breast
 Chicken Parmesan | italian breadcrumbs
 Chicken Parmesan | egg
 Chicken Parmesan | shredded mozzarella
 Chicken Parmesan | pasta sauce

A simple cursor would end up showing "Chocolate Milk" twice and "Chicken Parmesan" five times. This is definitely not what I want. I want each of them to display once with all the ingredients comma-separated. i.e.:

 recipe           | ingredients
------------------+---------------------------------------------------------------------------
 Chocolate Milk   | milk, chocolate syrup
 Chicken Parmesan | chicken breast, italian breadcrumbs, egg, shredded mozzarella, pasta sauce
Solution

  • What you need is group_concat: http://www.sqlite.org/lang_aggfunc.html

    This should work:

    SELECT r._id, r.name AS recipe, group_concat(i.name, ",") AS ingredient
FROM recipes r
INNER JOIN recipes_ingredients ri ON ri.recipe_id=r._id
INNER JOIN ingredients i ON ri.ingredient_id=i._id
GROUP BY r._id;