Cookbook

After preparing breakfast this morning, I decided that each day I would create one small project, or one next version of existing projects. Each such project will be distributed without restrictions and will be accompanied by a brief report on its creation. Therefore, it is quite logical to make your first project suitable for storing culinary recipes.

General description

Thousands of recipes have been invented throughout the history of mankind, and this process continues today. Fashion changes, ideas about health change, but cooking is a wonderful device that can take you to another country or even an era.

That’s why it’s cool to have your own trusted food guide on hand!

Business model

Adding and storing recipes, including composition, image and cooking technologies.

Search for recipes by name, by available ingredients, by ingredient type, by recipe category.

Data Model

What will we store:
Recipe

• Name
• Description
• Picture
• Technology description
• Total cooking time
• Author

Ingredients

• Ingredient
• Recipe
• Amount
• Measure

Measure

• Name

Recipe categories

• Recipe
• Category

Category

• Name

Ingredient

• Name
• Ingredient type
• Picture
• Description

Ingredient view

• Name

Author

• Name
• Photo
• Description
• Contact

Data Presentation

A list of recipes, a search (filtering) system will be placed on the main form. Viewing / editing a recipe through the editing form.

References are displayed in modal windows. The window is opened by the button next to the data selection field from the directory.

Viewing/editing the directory is carried out through the editing form.

The minimum size of the main form is 1280×720 (HD)

Project implementation

Database

Data Schema

Main form

The main form contains: a table for displaying recipes (1), a toolbar (2), a filtering panel (3). There are three buttons on the toolbar: for adding (3), editing (4) and deleting (5) recipes. On the filtering panel there is a field for searching by name (7), a multi-pager with two tabs (8) and an invisible button for obtaining data for display (9). Each multipage tab contains a table (10) for displaying filtering options (recipe category and recipe content).

Data filtering is carried out by the name of the recipe (search by entry), as well as using auxiliary tables: categories and ingredients. This will allow you to find recipes that meet the specified criteria. For example: show all vegetarian desserts (2 categories) that include banana, apple and peach (3 ingredients).

To do this, the search is carried out through a SQL query, and the query itself is built programmatically in the btnSearch button click handler, taking into account what kind of filtering is required – by name, by category, by ingredients, or all at once.

procedure frmMain_btnSearch_OnClick (Sender: TObject; var Cancel: boolean);
var
  tmpSQL : string;
begin
  tmpSQL := '';
  // if ingredients and categories are not used for the search, then we create a simple query with a search by name
  if (IngredientIDs.Count = 0) and (CategoryIDs.Count = 0) then
  begin
    tmpSQL := tmpSQL + 'SELECT recipe.name, recipe.id FROM recipe ';
    tmpSQL := tmpSQL + 'WHERE (1=1) ';
    if frmMain.edtName.Text <> '' then
      tmpSQL := tmpSQL + 'and (name LIKE ''%'+frmMain.edtName.Text+'%'')';
  end
  else // request is complex
  begin
    // data on ingredients
    if (IngredientIDs.Count > 0) then
    begin
      tmpSQL := tmpSQL +' SELECT name, id FROM ( ';
      tmpSQL := tmpSQL + 'SELECT recipe.name, recipe.id, count(recipe.id) as rcount FROM recipe ';
      tmpSQL := tmpSQL + 'LEFT JOIN recipeIngredient ON recipeIngredient.id_recipe = recipe.id ';
      tmpSQL := tmpSQL + 'WHERE (1=1) ';
      if frmMain.edtName.Text <> '' then
        tmpSQL := tmpSQL + 'and (name LIKE ''%'+frmMain.edtName.Text+'%'')';
      tmpSQL := tmpSQL + 'and ( recipeIngredient.id_ingredient in ('+IngredientIDs.CommaText+') ) ';
      tmpSQL := tmpSQL + 'GROUP BY recipe.name, recipe.id ';
      tmpSQL := tmpSQL + 'HAVING rcount = '+IntToStr(IngredientIDs.Count);
      tmpSQL := tmpSQL +' ) ';
    end;
    // if we filter by ingredients and categories, then data intersection will be required
    if (IngredientIDs.Count > 0) and (CategoryIDs.Count > 0) then
      tmpSQL := tmpSQL + ' INTERSECT ';
    // category data
    if CategoryIDs.Count > 0 then
    begin
      tmpSQL := tmpSQL +' SELECT name, id FROM ( ';
      tmpSQL := tmpSQL + 'SELECT recipe.name, recipe.id, count(recipe.id) as rcount FROM recipe ';
      tmpSQL := tmpSQL + 'LEFT JOIN recipeCategory ON recipeCategory.id_recipe = recipe.id ';
      tmpSQL := tmpSQL + 'WHERE (1=1) ';
      if frmMain.edtName.Text <> '' then
        tmpSQL := tmpSQL + 'and (name LIKE ''%'+frmMain.edtName.Text+'%'')';
      tmpSQL := tmpSQL + 'and ( recipeCategory.id_category in ('+CategoryIDs.CommaText+') )';
      tmpSQL := tmpSQL + 'GROUP BY recipe.name, recipe.id ';
      tmpSQL := tmpSQL + 'HAVING rcount = '+IntToStr(CategoryIDs.Count);
      tmpSQL := tmpSQL +' ) ';
    end;
  end;
  frmMain.btnSearch.dbSQL := tmpSQL;
end;Code language: Delphi (delphi)

Recipe editing form

On the editing form, in addition to the usual data entry elements, there are special additional elements.

Next to the drop-down list (1) there is a button for calling the directory (2). This approach allows you to add new elements of the directory in the process of data entry, without resorting to a special menu item on the main form for maintaining directories. This principle is used on all editing forms that contain elements for selecting data from related tables.

If it is necessary to display a subordinate table on the editing form, then a panel is placed in the right place, on the surface of which the form with all the necessary elements will be located: a data display table and a toolbar with buttons. This solution allows you not to overload individual forms with a large number of elements, which makes the project itself more convenient.

In this case, two forms are placed on the project editing form: recipe ingredients and recipe categories. Forms are placed on panels programmatically at application startup.

Recipe ingredients

This form, like all tabular data display forms, contains a table (1) and a toolbar with control buttons (2). Since data is filtered in this table by a specific recipe, the button with the “Search” function (4) and the invisible field for storing id_recipe (3), which is used in the filter, are used to display the data.

Using an input field instead of a drop-down list makes the program a little faster, since each drop-down list is updated both when the program is launched and when the table corresponding to it is edited. Therefore, where this element is invisible, I recommend using TdbEdit instead of TdbCombobox. In this case, the name of the id_recipe field required in this case will need to be typed manually, since it will not be in the list of fields.

And the desired value will be placed in this component when the recipe editing form is opened, in the onShow handler.

procedure frmRecipeEdit_OnShow (Sender: TObject; Action: string);
begin
  // customize the display of ingredients
  frmRecipeIngredient.edtIDRecipe.Text := IntToStr( frmRecipeEdit.btnSave.dbGeneralTableId );
  frmRecipeIngredient.btnSearch.Click;
  // customize the display of categories
  frmRecipeCategory.edtIDRecipe.Text := IntToStr( frmRecipeEdit.btnSave.dbGeneralTableId );
  frmRecipeCategory.btnSearch.Click;
end;Code language: Delphi (delphi)

Recipe categories

This form differs from all other forms of displaying tabular data in that it serves only to display data, and its editing is carried out programmatically. The user checks the checkboxes in the category directory, and the selected data is transferred to the recipe categories

Categories

Checkboxes have been added to the table on this form, which are used for bulk editing of the RecipeCategory table.

When the form is displayed, the checkboxes are set according to the information received from the table on the recipe editing form, and when the “Select” button is pressed, the data in the database is replaced.

procedure frmCategory_OnShow (Sender: TObject; Action: string);
// Show form
var
  i,j: integer;
begin
  // show data
  for i := 0 to frmCategory.tgrMain.RowCount - 1 do
  begin
    frmCategory.tgrMain.cell[0,i].asBoolean := False;
    for j := 0 to frmRecipeCategory.tgrMain.RowCount - 1 do
    begin
      if frmCategory.tgrMain.cells[1,i] = frmRecipeCategory.tgrMain.cells[1,j] then
      begin
        frmCategory.tgrMain.cell[0,i].asBoolean := True;
        Break;
      end;
    end;
  end;
end;Code language: Delphi (delphi)

procedure frmCategory_btnSelect_OnClick (Sender: TObject; var Cancel: boolean);
var
  tmpSQL: string;
  i: integer;
begin
  // delete old
  tmpSQL := 'DELETE FROM RecipeCategory WHERE id_recipe = '+frmRecipeCategory.edtIDRecipe.Text;
  SQLExecute(tmpSQL);
  // add new
  for i := 0 to frmCategory.tgrMain.RowCount - 1 do
  begin
    if frmCategory.tgrMain.cell[0,i].asBoolean then
    begin
      tmpSQL := 'INSERT INTO RecipeCategory (id_recipe,id_category) VALUES ('+frmRecipeCategory.edtIDRecipe.Text+','+IntToStr(frmCategory.tgrMain.dbIndexToID(i))+') ';
      SQLExecute(tmpSQL);
    end;
  end;
  frmRecipeCategory.btnSearch.Click;
end;Code language: Delphi (delphi)

Result

To achieve a better visual effect, the Luna style has been activated in the project, the font size has been increased, and the option to display the header HeaderStyle = hsOffice2007

A link is available to download application archive.

For readers of My Visual Database Library project with all scripts.