jeudi 31 mars 2016

SQLite - Select with JOINs to Many-to-Many and One-to-Many

I've got a simple recipe database that I'm attempting to write a query for that will pull in a recipe, as well as all the associated ingredients and steps. The recipe itself has a many-to-many relationship with ingredients (to promote re-use of ingredient entries) and a one-to-many relationship with steps (chances are one recipe does not have an identical step to another, so no need for re-use here).

The database is setup like so:

CREATE TABLE Recipe (
    _id INTEGER PRIMARY KEY,
    recipe_name TEXT UNIQUE NOT NULL
);

CREATE TABLE Ingredient (
    _id INTEGER PRIMARY KEY,
    ingredient TEXT UNIQUE NOT NULL
);

CREATE TABLE RecipeIngredient (
    _id INTEGER PRIMARY KEY,
    recipe_id INTEGER,
    ingredient_id INTEGER,
    amount REAL,
    FOREIGN KEY (recipe_id) REFERENCES Recipe(_id),
    FOREIGN KEY (ingredient_id) REFERENCES Ingredient(_id)
);

CREATE TABLE Step (
    _id INTEGER PRIMARY KEY,
    instruction TEXT NOT NULL,
    number INTEGER NOT NULL,
    recipe_id INTEGER,
    FOREIGN KEY (recipe_id) REFERENCES Recipe(_id)
);

I'm able to pull all the information for a given Recipe by _id using INNER JOINS on all the tables (which creates a ton of duplicate data), but where I'm stuck is how can I group/order this to make the data useful without requiring additional parsing of the results to get a meaningful object? Would this just be better to do in 2 queries (one for the ingredients and one for the steps)?

UPDATE

Adding some sample data that comes back from selecting one recipe ID, doing INNER JOINs between the tables (ordered by ingredient/step number):

|| recipe ||    || ingredient||  ||step||   ||step number||
"Recipe One"    "Ingredient 1"   "Step 1"         "1"
"Recipe One"    "Ingredient 1"   "Step 2"         "2"
"Recipe One"    "Ingredient 1"   "Step 3"         "3"
"Recipe One"    "Ingredient 2"   "Step 1"         "1"
"Recipe One"    "Ingredient 2"   "Step 2"         "2"
"Recipe One"    "Ingredient 2"   "Step 3"         "3"
"Recipe One"    "Ingredient 3"   "Step 1"         "1"
"Recipe One"    "Ingredient 3"   "Step 2"         "2"
"Recipe One"    "Ingredient 3"   "Step 3"         "3"

1 commentaire:

  1. Sqlite: Sqlite - Select With Joins To Many-To-Many And One-To-Many >>>>> Download Now

    >>>>> Download Full

    Sqlite: Sqlite - Select With Joins To Many-To-Many And One-To-Many >>>>> Download LINK

    >>>>> Download Now

    Sqlite: Sqlite - Select With Joins To Many-To-Many And One-To-Many >>>>> Download Full

    >>>>> Download LINK Gs

    RépondreSupprimer