RocketSled Tutorial 5: Relational Databases With The Nozzle

Author: David Grinton thegrinch@workingsoftware.com.au

Table of Contents

• Introduction
• Modify the tables
• The DbRelationshipObject
• Update existing DB objects
• Prevent duplicate ingredients

Introduction

Welcome to RocketSled Tutorial 5.

In this tutorial we will modify the tables and classes set up in RocketSled Tutorial 4 to use a many to many relationship between SugaryPastry and Ingredient.

Modify the tables

First we need to modify the ingredient table and add a new table to link the ingredient and sugary_pastry tables.

$ echo "DROP TABLE ingredient;" | mysql -u user --password=dbpass pastries
$ echo "CREATE TABLE ingredient (ingredient_id int(6) NOT NULL PRIMARY KEY
auto_increment, ingredient_name varchar(50) NOT NULL);" | mysql -u user
--password=dbpass pastries
$ echo "CREATE TABLE pastry_ingredient (pastry_id int(6) NOT NULL,
ingredient_id int(6) NOT NULL, PRIMARY KEY(pastry_id,ingredient_id));" |
mysql -u user --password=dbpass pastries

The DbRelationshipObject

We will need a new class to represent the table which links between the ingredient and sugary_pastry tables. Create WEBROOT/yourdir/packages/my/pastry_ingredient.class.php with the following code:

<?php
   class PastryIngredient extends DbRelationshipObject
   {
      function PastryIngredient()
      {
         $this->DbRelationshipObject();
      }
   }
?>

Update existing DB objects

Next we need to modify the Ingredient and SugaryPastry classes to reflect the changed relationship between ingredient/sugary_pastry/pastry_ingredient:

<?php
   class SugaryPastry extends MyDbObject
   {
      public function SugaryPastry()
      {
         parent::MyDbObject('SugaryPastry');
         $this->join('Ingredient','PastryIngredient');
      }
 
      public function toString()
      {
         return $this->get('pastry_name');
      }
   }
?>

<?php
   class Ingredient extends MyDbObject
   {
      public function Ingredient()
      {
         parent::MyDbObject('Ingredient');
         $this->join('SugaryPastry','PastryIngredient');
      }
 
      public function toString()
      {
         return $this->get('ingredient_name');
      }
   }
?>

Note that we have changed the depend() and support() lines to join(), where the first argument of DbObject::join() is the class we are joining to and the second is the class/table which the join uses.

Prevent duplicate ingredients

We will need to slightly modify the AddPastry class to prevent duplicate ingredient_names from being added (but we will allow duplicate pastry_names):

public function performHandlerTasks()
{
   if(Application::formPosted())
   {
      $form = Form::load('my.NewPastry');
 
      if ($form->validate())
      {
         $p = new SugaryPastry();
         $p->parse();
 
         foreach (explode("\n",Application::param('ingredients')) as
         $ingredient)
         {
            $ingredient = trim($ingredient);
            $i = new Ingredient();
            $i->clauseSafe('ingredient_name',$ingredient);
 
            if(!$i->id())
                $i->setSafe('ingredient_name',$ingredient);
 
            $p->add($i);
         }
         $p->save();
 
         Application::redirect('ListPastries');
      }
   }
}

We can use clauseSafe() to check if an Ingredient with this ingredient_name already exists, if not we add the Ingredient. The add() method takes care of the other required operations, based on the join()s we set up in the Ingredient/SugaryPastry classes. The ListPastries class/RSML files do not require changes.