SQLDelight Relationships

Question

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.

Damson

Answer 1

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_ } 
    }  

Rajkot

I approve ! Kotlin is very good with this. – Demoss

If I understood correct, this would mean I have to do 2 queries? Of course Kotlin is good at this, but this would most likely mean a worse performance than one query. – Damson

No, you can still use your only SELECT query. It will return you the list of rows that SQLDelight maps to List<SelectWithIngredients>. And you'll have a name of recipe in each row. So you'll just need to transform it to get Recipe or use as is. – Rajkot

Ah yes. That should be sufficient. Thanks! – Damson