Search code examples
androidkotlinrelationshipsqldelight

SQLDelight Relationships

I want to model relationships with SQLDelight, especially a one to many relationship.

I have 2 tables: recipe and ingredient. For simplicity they look like this:

CREATE TABLE recipe (
  id INTEGER NOT NULL PRIMARY KEY,
  name TEXT NOT NULL
)

CREATE TABLE ingredient (
  recipe_id INTEGER NOT NULL,
  name TEXT NOT NULL,
  FOREIGN KEY (recipe_id) REFERENCES recipe(id) ON DELETE CASCADE
);

So I have a list of recipes and each recipe can contain 0-n ingredients.

I have 2 goals:

  • Write a recipe with all its ingredients
  • Read a recipe with all its ingredients

I'm pretty sure the first one can only be done manually, e.g. insert the recipe then manually insert the associating ingredients.

For the latter I try to join the tables with this statement:

selectWithIngredients:
SELECT *
FROM recipe
INNER JOIN ingredient ON recipe.id = ingredient.recipe_id
WHERE recipe.id=?;

For which SQLDelight generates me a 1:1 relationship file:

public data class SelectWithIngredients(
  public val id: Long,
  public val name: String,
  public val recipe_id: Long,
  public val name_: String,
)

Is there any nice way to retrieve the data (recipe + list of ingredients) with one generated function? Something similar to Rooms @Embedded and @Relation annotations.

Solution

  • Unfortunately, SQLDelight is not so exquisite. All it does is give you a data class for each row of a query, if you want to do more complicated logic like mapping one table to a list of some other table than you will need to do that yourself in kotlin.

    E.g. having Recipe like this

    data class Recipe(val name: String, val ingredients: List<String>)

    You can do this with your select

    val rows: List<SelectWithIngredients> 

rows.groupBy { it.name }
    .map { (recipe, selectRows) -> 
            Recipe(name, selectRows.map { it.name_ } 
    }