====== Implementing Many-To_Many Relationships ======

====== Background ======

There have been a number of questions about this in the Forum, so I thought I would write a short tutorial on what I have learned so far. Let me caution you, I am new to Akelos and new to RoR but this all seems to work for me. If there are errors or better ways to do this, please make the necessary changes.

What follows is just a description of the linkages that need to be made to get things to work. There is no validation or error checking.

====== Scenario ======

For my many-to-many setting, lets say I have a violin shop and I want to keep track of my inventory. I have violins, violas, cellos, violin_bows, strings, rosin,etc. So I have a **Products** table. To keep it simple, let's just give it several attributes like //name//, //description//, //price// and //discounted_price//.

I would also like to categorize the products. Categories might be instruments, bows, accessories, sale items, clearance items, etc. This gives me a **Categories** table. Again, for the sake of simplicity, it will only have //name//, as an attribute.  

Each category can have many products and each product can belong to more than one category.

====== Database ======
 
I created the necessary databases called //pc//, //pc_dev//, and //pc_tests//. The installer for this example looks as follows:

<code php>
class PcInstaller extends AkInstaller
{
    function up_1()
    {

	$this->createTable('categories', "
          id,
          name varchar(100) 
        ");

        $this->createTable('products', "
          id, 
          name varchar(100),
          description varchar(1000),
          price numeric(12,2),
          discounted_price numeric(12,2),
        "); 

	$this->createTable('categories_products', "
          id,
          category_id,
          product_id
        ");
 
    }


    function down_1()
    {
      $this->dropTables('categories', 'products', 'categories_products');  
    }

}
</code>
Notice the junction table **categories_products** has an attribute //id// . I normally don't put and id attribute in a junction table but Akelos seems to require it. I got errors in the display code when it wasn't present. Also note that I set the price fields to numeric(12,2). This is in case I ever get a hold of a Stradivari or del Gesu violin.

Run the migrate script on this installer and you should see your tables in your database.

I am using a Postgres database and the application phpPgAdmin. So to start things off, let's go ahead and add some categories. I added, //Instruments//, //Bows//, //Accessories//, //Sale Items// and //Clearance Items//. 

====== Akelos ======

===== Scaffold =====

We can now use Akelos to generate the scaffolds.

./script/generate scaffold Category

./script/generate scaffold Product

===== Model =====

The first step is to tell the models that we have a many-to-many relationship. Open up the model files and modify them as below:

<code php>
<?php

class Category extends ActiveRecord
{
  var $has_and_belongs_to_many = 'products';
}

?>

<?php

class Product extends ActiveRecord
{
  var $has_and_belongs_to_many = 'categories';
}

?>
</code>

===== Controllers =====

The controllers also need to know what models to load. Add the line:

<code php>var $models = array('category', 'product');</code>

at the beginning of category_controller.php and product_controller.php.

The basic application will work now. We can see categories and can create products. We still can't associate the two. We need to make a few more modifications to finish the wiring.

===== Attaching Categories to Products =====

The first thing I would like to do is create a new product and assign it to categories. The route I chose to do this was to get all of the **Categories** and display them in a multiple select box. The method for getting all of the **Categories** is:

<code php> $Category->collect($Category->find())</code>

Edit the _form.tpl file in the //views/product// directory and add the line:

<code php>
echo $form_options_helper->select('product', 'category_id', $Category->collect($Category->find(), 'name', 'id'),array(),array('multiple'=>'true'));
</code>

I added this line right under the one that generated the //name// and //description// input boxes. This should give you a multiple select box with all of the categories in it when you try to create a new product. If you now try to create a new product and select a couple of categories and press **OK** , you will see that the product is saved but the **categories_products** table has not been updated. That is the next step.

===== Getting the Association To Work =====
The **Product** object knows about **Categories** because we told it to in the first line of the controller. We just need to assign the values we selected in the form to the attributes in the model. We do this with the following line of code:

<code php>$this->Product->category->set($this->Category->find($this->params['product']['category_id']));</code>

The values we selected in the multi-select box are passed in the variable **params['product']['category_id']**. We collect all of these from the **Category** table and set them in the **Categories** collection in the **Product** object. In Akelos the plural object (in this case **Categories**) is the collection but the singular object (in this case **Category**) is the one that knows how to do stuff.

Edit the product_controller.php file and modify the add() method so it looks as follows:
<code php>
function add()

    {
     
        if(!empty($this->params['product'])){
            $this->Product->setAttributes($this->params['product']);

	    $this->Product->category->set($this->Category->find($this->params['product']['category_id']));

            if ($this->Request->isPost()  && $this->Product->save()) {
	      $this->flash['notice'] = $this->t('Product was successfully created.');
                $this->redirectTo(array('action' => 'show', 'id' => $this->Product->getId()));
            }
        }
    }
</code>
Now when you create a new product and press **OK**, everything should work properly and you will see that the **categories_products** table was updated.


====== Get all categories belonging to a product ======

This is easy, if you know how :-)
<code php>
$this->Product->category->load();
</code>
The method loads a collection with all related categories.
Now you can loop over the collection:
<code php>
foreach (array_keys ($Product->categories) as $k) {
  $category_name = $Product->categories[$k]->get('name');
}
</code>

====== Things To Do ======
As I said before, There is no error checking in this example. We also still need to modify the product view code so it displays the categories for each product. We also need to modify the category view code so that it can display the products for a given category. I will leave that as an exercise for the interested student.