Lesson Weekend

So far, we have learned how to do one-to-many relationships in our projects. For example, one category can have many tasks in it. Let's say now I want a task to be in multiple categories, as well as categories have multiple tasks. This is a situation where we'd use a many-to-many relationship.

We already have our to_do database with tables for tasks and for categories, but right now we are using category_id to create a relationship between a task and a category. We will actually need to remove that column because we are going to create a join table to store the relationships between our tasks and our categories. We covered join tables in the Database relations with SQL lesson, if you would like to go back and refresh your memory.

Let's start by removing the category_id column from the tasks table:

to_do=# ALTER TABLE tasks DROP category_id;
to_do=# DROP DATABASE to_do_test;

Create a test database again from phpMyAdmin.

Now we should be ready to create a many-to-many relationship between these two tables. To do this, we need to update our database to hold a join table called categories_tasks so that we can store both category_id and task_id together in a single record to represent a category/task relationship.

to_do=# CREATE TABLE categories_tasks (id serial PRIMARY KEY, category_id int, task_id int);
to_do=# DROP DATABASE to_do_test;

Once more, create a test database from phpMyAdmin.

Now that our database is set up for the many-to-many relationship, we can start altering methods in our classes to interact with the join table.

Let's start with our Task class. Here are the tests with references to category_id removed.

tests/TaskTest.php
<?php

    /**
    * @backupGlobals disabled
    * @backupStaticAttributes disabled
    */

    require_once "src/Task.php";
    require_once "src/Category.php";

    $server = 'mysql:host=localhost:8889;dbname=to_do_test';
    $username = 'root';
    $password = 'root';
    $DB = new PDO($server, $username, $password);

    class TaskTest extends PHPUnit_Framework_TestCase
    {

        protected function tearDown()
        {
            Task::deleteAll();
            Category::deleteAll();
        }

        function testGetDescription()
        { 
            //Arrange
            $description = "Do dishes.";
            $test_task = new Task($description);

            //Act
            $result = $test_task->getDescription();

            //Assert
            $this->assertEquals($description, $result);
        }

        function testSetDescription()
        { 
            //Arrange
            $description = "Do dishes.";
            $test_task = new Task($description);

            //Act
            $test_task->setDescription("Drink coffee.");
            $result = $test_task->getDescription();

            //Assert
            $this->assertEquals("Drink coffee.", $result);
        }

        function testGetId()
        {
            //Arrange
            $description = "Watch the new Thor movie.";
            $test_task = new Task($description);
            $test_task->save();

            //Act
            $result = $test_task->getId();

            //Assert
            $this->assertTrue(is_numeric($result));
        }

        function testSave()
        {
            //Arrange
            $description = "Eat breakfast";
            $test_task = new Task($description);

            //Act
            $executed = $test_task->save(); 

            // Assert
            $this->assertTrue($executed, "Task not successfully saved to database");
        }

        function testGetAll()
        {
            //Arrange
            $description = "Wash the dog";
            $test_task = new Task($description);
            $test_task->save();


            $description2 = "Water the lawn";
            $test_task2 = new Task($description2);
            $test_task2->save();

            //Act
            $result = Task::getAll();

            //Assert
            $this->assertEquals([$test_task, $test_task2], $result);
        }

        function testDeleteAll()
        {
            //Arrange
            $description = "Wash the dog";
            $test_task = new Task($description);
            $test_task->save();

            $description2 = "Water the lawn";
            $test_task2 = new Task($description2);
            $test_task2->save();

            //Act
            Task::deleteAll();

            //Assert
            $result = Task::getAll();
            $this->assertEquals([], $result);
        }

        function testFind()
        {
            //Arrange
            $description = "Wash the dog";
            $test_task = new Task($description);
            $test_task->save();

            $description2 = "Water the lawn";
            $test_task2 = new Task($description2);
            $test_task2->save();

            //Act
            $result = Task::find($test_task->getId());

            //Assert
            $this->assertEquals($test_task, $result);
        }

        function testUpdate()
        {
            //Arrange
            $description = "Wash the dog";
            $test_task = new Task($description);
            $test_task->save();

            $new_description = "Clean the dog";

            //Act
            $test_task->update($new_description);

            //Assert
            $this->assertEquals("Clean the dog", $test_task->getDescription());
        }

        function test_deleteTask()
        {
            //Arrange
            $description = "Wash the dog";
            $test_task = new Task($description);
            $test_task->save();

            $description2 = "Water the lawn";
            $test_task2 = new Task($description2);
            $test_task2->save();


            //Act
            $test_task->delete();

            //Assert
            $this->assertEquals([$test_task2], Task::getAll());
        }

    }
?>

Now here is the class.

src/Task.php
<?php
    class Task
    {
        private $description;
        private $id;

        function __construct($description, $id = null)
        {
            $this->description = $description;
            $this->id = $id;
        }

        function setDescription($new_description)
        {
            $this->description = (string) $new_description;
        }

        function getDescription()
        {
            return $this->description;
        }

        function getId()
        {
            return $this->id;
        }

        function save()
        {
            $executed = $GLOBALS['DB']->exec("INSERT INTO tasks (description) VALUES ('{$this->getDescription()}');");
            if ($executed) {
                return true;
            } else {
                return false;
            }
        }

        static function getAll()
        {
            $returned_tasks = $GLOBALS['DB']->query("SELECT * FROM tasks;");
            $tasks = array();
            foreach($returned_tasks as $task) {
                $description = $task['description'];
                $id = $task['id'];
                $new_task = new Task($description, $id);
                array_push($tasks, $new_task);
            }
            return $tasks;
        }

        static function deleteAll()
        {
            $executed = $GLOBALS['DB']->exec("DELETE FROM tasks;");
            if ($executed) {
                return true;
            } else {
                return false;
            }
        }

        static function find($search_id)
        {
            $found_task = null;
            $returned_tasks = $GLOBALS['DB']->prepare("SELECT * FROM tasks WHERE id = :id");
            $returned_tasks->bindParam(':id', $search_id, PDO::PARAM_STR);
            $returned_tasks->execute();
            foreach($returned_tasks as $task) {
                $description = $task['description'];
                $id = $task['id'];
                if ($id == $search_id) {
                   $found_task = new Task($description, $id);
                }
            }
            return $found_task;
         }

        function update($new_description)
        {
            $executed = $GLOBALS['DB']->exec("UPDATE tasks SET name = '{$new_description}' WHERE id = {$this->getId()};");
            if ($executed) {
               $this->setDescription($new_description);
               return true;
            } else {
               return false;
            }
        }

        function delete()
        {
            $GLOBALS['DB']->exec("DELETE FROM tasks WHERE id = {$this->getId()};");
             if ($executed) {
                return true;
            } else {
                return false;
            }
        }
    }
?>

We removed category_id from the __construct method as well as from the save() and getAll() methods. We also removed the getter and setter methods for the category ID.

Now, here are our tests for Category and the class file with all references to category_id removed.

Note that Category still has an id property, and each Category object is still stored in the database with an id value assigned to it, we are just no longer using the variable category_id in our code because we will be linking our two classes in a different way.

tests/CategoryTest.php
<?php

    /**
    * @backupGlobals disabled
    * @backupStaticAttributes disabled
    */

    require_once "src/Category.php";
    require_once "src/Task.php";

    $server = 'mysql:host=localhost8889;dbname=to_do_test';
    $username = 'root';
    $password = 'root';
    $DB = new PDO($server, $username, $password);


    class CategoryTest extends PHPUnit_Framework_TestCase
    {

        protected function tearDown()
        {
          Category::deleteAll();
          Task::deleteAll();
        }

        function testGetName()
        { 
            //Arrange
            $name = "Kitchen chores";
            $test_category = new Category($name);

            //Act
            $result = $test_category->getName();

            //Assert
            $this->assertEquals($name, $result);

        }

        function testSetName()
        { 
            //Arrange
            $name = "Kitchen chores";
            $test_category = new Category($name);

            //Act
            $test_category->setName("Home chores");
            $result = $test_category->getName();

            //Assert
            $this->assertEquals("Home chores", $result);
        }

        function testGetId()
        {
            //Arrange
            $name = "Work stuff";
            $test_category = new Category($name);
            $test_category->save();

            //Act
            $result = $testTask->getId();

            //Assert
            $this->assertTrue(is_numeric($result));
        }

        function testSave()
        {
            //Arrange
            $name = "Work stuff";
            $test_category = new Category($name);
            $test_category->save();

            //Act
            $executed = $test_category->save(); 

            // Assert
            $this->assertTrue($executed, "Category not successfully saved to database");
        }

        function testUpdate()
        {
            //Arrange
            $name = "Work stuff";
            $test_category = new Category($name);
            $test_category->save();

            $new_name = "Home stuff";

            //Act
            $test_category->update($new_name);

            //Assert
            $this->assertEquals("Home stuff", $test_category->getName());
        }

        function testDeleteCategory()
        {
            //Arrange
            $name = "Work stuff";
            $test_category = new Category($name);
            $test_category->save();

            $name2 = "Home stuff";
            $test_category2 = new Category($name2);
            $test_category2->save();


            //Act
            $test_category->delete();

            //Assert
            $this->assertEquals([$test_category2], Category::getAll());
        }

        function testGetAll()
        {
            //Arrange
            $name = "Work stuff";
            $name2 = "Home stuff";
            $test_category = new Category($name);
            $test_category->save();
            $test_category2 = new Category($name2);
            $test_category2->save();

            //Act
            $result = Category::getAll();

            //Assert
            $this->assertEquals([$test_category, $test_category2], $result);
        }

        function testDeleteAll()
        {
            //Arrange
            $name = "Wash the dog";
            $test_category = new Category($name);
            $test_category->save();

            $name2 = "Water the lawn";
            $test_category2 = new Category($name2);
            $test_category2->save();

            //Act
            Category::deleteAll();

            //Assert
            $result = Category::getAll();
            $this->assertEquals([], $result);
        }

        function testFind()
        {
            //Arrange
            $name = "Wash the dog";
            $test_category = new Category($name);
            $test_category->save();

            $name2 = "Home stuff";
            $test_category2 = new Category($name2);
            $test_category2->save();

            //Act
            $result = Category::find($test_category->getId());

            //Assert
            $this->assertEquals($test_category, $result);
        }
    }

?>

And here is the class:

src/Category.php
<?php
    class Category
    {
        private $name;
        private $id;

        function __construct($name, $id = null)
        {
            $this->name = $name;
            $this->id = $id;
        }

        function setName($new_name)
        {
            $this->name = (string) $new_name;
        }

        function getName()
        {
            return $this->name;
        }

        function getId()
        {
            return $this->id;
        }

        function save()
        {
              $executed = $GLOBALS['DB']->exec("INSERT INTO categories (name) VALUES ('{$this->getName()}');");
              if ($executed) {
                  $this->id = $GLOBALS['DB']->lastInsertId();
                  return true;
              } else {
                  return false;
              }
        }

        function update($new_name)
        {
              $executed = $GLOBALS['DB']->exec("UPDATE categories SET name = '{$new_name}' WHERE id = {$this->getId()};");
              if ($executed) {
                  $this->setName($new_name);
                  return true;
              } else {
                  return false;
              }   
        }

        function delete()
        {
              $executed = $GLOBALS['DB']->exec("DELETE FROM categories WHERE id = {$this->getId()};");
              if ($executed) {
                  return true;
              } else {
                  return false;
              }
        }

        static function getAll()
        {
            $returned_categories = $GLOBALS['DB']->query("SELECT * FROM categories;");
            $categories = array();
            foreach($returned_categories as $category) {
                $name = $category['name'];
                $id = $category['id'];
                $new_category = new Category($name, $id);
                array_push($categories, $new_category);
            }
            return $categories;
        }

        static function deleteAll()
        {
              $executed = $GLOBALS['DB']->exec("DELETE FROM categories;");
              if ($executed) {
                  return true;
              } else {
                  return false;
              }
        }

        static function find($search_id)
        {
            $found_category = null;
            $returned_categories = $GLOBALS['DB']->prepare("SELECT * FROM categories WHERE id = :id");
            $returned_categories->bindParam(':id', $search_id, PDO::PARAM_STR);
            $returned_categories->execute();
            foreach($returned_categories as $category) {
                $name = $category['name'];
                $id = $category['id'];
                if ($id == $search_id) {
                  $found_category = new Category($name, $id);
                }
            }
            return $found_category;
        }
    }
?>

We removed the getTasks method and the testDeleteCategoryTasks test for now. Phew...now we can actually write code to connect these two classes.

We will start by creating an addTask method to our Category class, so that we can associate a task with a category. Here is the spec:

tests/CategoryTest.php
function testAddTask()
{
    //Arrange
    $name = "Work stuff";
    $test_category = new Category($name);
    $test_category->save();

    $description = "File reports";
    $test_task = new Task($description);
    $test_task->save();

    //Act
    $test_category->addTask($test_task);

    //Assert
    $this->assertEquals($test_category->getTasks(), [$test_task]);
}

You will notice that we had to call the method getTasks() in the assertEquals line of this spec. We haven't made that method yet, so let's write a spec for it now as well. Sometimes you have to write two specs at the same time and then two methods to make them pass. We want to be able to call getTasks() on a specific instance of a Category in order to get an array of all Task objects we have assigned to that Category. Here is our spec for getTasks() on the Category class:

tests/CategoryTest.php
function testGetTasks()
{
    //Arrange
    $name = "Home stuff";
    $test_category = new Category($name);
    $test_category->save();

    $description = "Wash the dog";
    $test_task = new Task($description);
    $test_task->save();

    $description2 = "Take out the trash";
    $test_task2 = new Task($description2);
    $test_task2->save();

    //Act
    $test_category->addTask($test_task);
    $test_category->addTask($test_task2);

    //Assert
    $this->assertEquals($test_category->getTasks(), [$test_task, $test_task2]);
}

Run your tests to make sure these are failing.

Now, let's change the addTask() method first:

src/Category.php
function addTask($task)
{
    $executed = $GLOBALS['DB']->exec("INSERT INTO categories_tasks (category_id, task_id) VALUES ({$this->getId()}, {$task->getId()});");
     if ($executed) {
        return true        
     } else {
        return false;
     }
}

When we want to make an association between an category and a task, we do it in the join table (categories_tasks). So instead of having to have a category_id attribute on an individual instance of Task, like we have been doing, we can just insert both the category_id and the task_id into the join table. When we want to know which tasks are associated with which categories, all we do is query the join table.

Let's do the getTasks() method on the Category class now:

function getTasks()
{
    $query = $GLOBALS['DB']->query("SELECT task_id FROM categories_tasks WHERE category_id = {$this->getId()};");
    $task_ids = $query->fetchAll(PDO::FETCH_ASSOC);

    $tasks = array();
    foreach($task_ids as $id) {
        $task_id = $id['task_id'];
        $result = $GLOBALS['DB']->query("SELECT * FROM tasks WHERE id = {$task_id};");
        $returned_task = $result->fetchAll(PDO::FETCH_ASSOC);

        $description = $returned_task[0]['description'];
        $id = $returned_task[0]['id'];
        $new_task = new Task($description, $id);
        array_push($tasks, $new_task);
    }
    return $tasks;
}
  1. We pull the task_ids from the join table wherever the category_id is $this->getId(). So for this particular category, all of the task_ids represent the tasks that belong to that category. They are returned as a PDO object that we then need to parse out all of the task_ids using the fetchAll method.
  2. We need to create an empty array to hold new Task objects.
  3. For each returned result, we loop through, grab the ID, query the tasks table with that ID, and then pull out the description of the task. Because the fetchAll method returns a nested array (meaning an array within an array), we need to select the first item in the array by saying $returned_task[0], and then we can select either the 'description' or the 'id' within that.
  4. Then we can create new Task objects and push them into our empty array.

Our tests should now be passing. Let's do the same thing now for the Task class. Here we want a method to assign a Category to a Task. Here are the two specs we need to write:

tests/TaskTest.php
function testAddCategory()
{
    //Arrange
    $name = "Work stuff";
    $test_category = new Category($name);
    $test_category->save();

    $description = "File reports";
    $test_task = new Task($description);
    $test_task->save();

    //Act
    $test_task->addCategory($test_category);

    //Assert
    $this->assertEquals($test_task->getCategories(), [$test_category]);
}

function test_getCategories()
{
    //Arrange
    $name = "Work stuff";
    $test_category = new Category($name);
    $test_category->save();

    $name2 = "Volunteer stuff";
    $test_category2 = new Category($name2);
    $test_category2->save();

    $description = "File reports";
    $test_task = new Task($description);
    $test_task->save();

    //Act
    $test_task->addCategory($test_category);
    $test_task->addCategory($test_category2);

    //Assert
    $this->assertEquals($test_task->getCategories(), [$test_category, $test_category2]);
}

And we can see that they are failing. Now the methods, which are very similar to the Category class methods:

src/Task.php
function addCategory($category)
{
    $GLOBALS['DB']->exec("INSERT INTO categories_tasks (category_id, task_id) VALUES ({$category->getId()}, {$this->getId()});");
     if ($executed) {
        return true        
     } else {
        return false;
     }
}

function getCategories()
{
    $query = $GLOBALS['DB']->query("SELECT category_id FROM categories_tasks WHERE task_id = {$this->getId()};");
    $category_ids = $query->fetchAll(PDO::FETCH_ASSOC);

    $categories = array();
    foreach($category_ids as $id) {
        $category_id = $id['category_id'];
        $result = $GLOBALS['DB']->query("SELECT * FROM categories WHERE id = {$category_id};");
        $returned_category = $result->fetchAll(PDO::FETCH_ASSOC);

        $name = $returned_category[0]['name'];
        $id = $returned_category[0]['id'];
        $new_category = new Category($name, $id);
        array_push($categories, $new_category);
    }
    return $categories;
}

Again, PDO objects can be set up in many different ways. You will find variations of this statement execution and fetch in the PDO documentation. Can you find a way that's dryer? How bout one that's more secure? Feel free to search around for a solution that works great for you!

Run the tests again and they should be passing. Now there is one last thing to change before we look at how to use many to many relationships in Silex. We need to update our delete() methods in both classes to make sure that they delete the associations we just created from the join table, as well as from their individual tables. Here is a test for each class:

tests/TaskTest.php
function testDelete()
{
    //Arrange
    $name = "Work stuff";
    $test_category = new Category($name);
    $test_category->save();

    $description = "File reports";
    $test_task = new Task($description);
    $test_task->save();

    //Act
    $test_task->addCategory($test_category);
    $test_task->delete();

    //Assert
    $this->assertEquals([], $test_category->getTasks());
}
tests/CategoryTest.php
function testDelete()
{
    //Arrange
    $name = "Work stuff";
    $test_category = new Category($name);
    $test_category->save();

    $description = "File reports";
    $test_task = new Task($description);
    $test_task->save();

    //Act
    $test_category->addTask($test_task);
    $test_category->delete();

    //Assert
    $this->assertEquals([], $test_task->getCategories());
}

To make them pass, here what you need to change:

src/Category.php
function delete()
{
    $executed = $GLOBALS['DB']->exec("DELETE FROM categories WHERE id = {$this->getId()};");
     if (!$executed) {
         return false;
     }
     $executed = $GLOBALS['DB']->exec("DELETE FROM categories_tasks WHERE category_id = {$this->getId()};");
     if (!$executed) {
         return false;
     } else {
         return true;
     }
}
src/Task.php
function delete()
{
    $GLOBALS['DB']->exec("DELETE FROM tasks WHERE id = {$this->getId()};");
     if (!$executed) {
         return false;
     }
    $GLOBALS['DB']->exec("DELETE FROM categories_tasks WHERE task_id = {$this->getId()};");
     if (!$executed) {
         return false;
     } else {
         return true;
     }
}

Now we are ready to start building our app in Silex!