cakePHP bind hasmany through relationship

Question

Ok this is kind of hard to explain but I'll try my best.

I have 3 tables

companies     products     product_availabilities
---------     --------     ----------------------
id            id           id
name          name         company_id
                           product_id
                           buys (tinyint)
                           sells (tinyint)

And their models

class Company extends AppModel
{
        public $name = 'Company';

        public $hasMany = array(
            'ProductAvailability'
        );


class Product extends AppModel
{       
    public $name = 'Product';

    public $hasMany = array(
        'ProductAvailability'
    );


class ProductAvailability extends AppModel
{
    public $name = 'ProductAvailability';

    public $belongsTo = array(
        'Company',
        'Product'
    );
}

What I want to do is when I create a company, I want to be able to select products that the company buys or sells. I've seen an example of a hasMany through relationship in the book (http://book.cakephp.org/1.3/view/1650/hasMany-through-The-Join-Model) but they are creating the form from the "join table" controller. Is it possible to bind the productAvailability model to my company model to be able to select the products while creating the company?

Edit : Here is how I've done it. I know it is not optimal as there is a lot of looping involved but it works.

Company controller :

    $products = $this->Company->ProductAvailability->Product->find('list', array('fields' => array('Product.id', 'Product.label')));
    $this->set('products', $products);

    if($this->request->is('post')){             
        if($this->Company->save($this->request->data)){
            foreach($products as $product)
            {
                $tmpArray = array(
                    'company_id' => $this->Company->id,
                    'product_id' => $product['Product']['id']
                );

                foreach($this->request->data('BuyProducts.product_id') as $buyProduct)
                {
                    if($buyProduct == $product['Product']['id'])
                        $tmpArray['buys'] = 1;
                }
                foreach($this->request->data('SellProducts.product_id') as $sellProduct)
                {
                    if($sellProduct == $product['Product']['id'])
                        $tmpArray['sells'] = 1;
                }

                if(count($tmpArray) > 2)
                {
                    $this->Company->ProductAvailability->create();
                    $this->Company->ProductAvailability->set($tmpArray);
                    $this->Company->ProductAvailability->save();
                }  
            }

            $this->Session->setFlash('Yay', 'success');
            $this->redirect(array('action' => 'index'));
        } else {
            $this->Session->setFlash('Nay', 'error');
        }
    }

Company add form :

<?php echo $this->Form->create('Company'); ?>

<?php echo $this->Form->input('name', array( 'div' => 'full-form')); ?>

<?php echo $this->Form->input('BuyProducts.product_id', array('multiple' => 'checkbox', 'options' => $products, 'div' => 'full-form', 'label' => false)); ?>

<?php echo $this->Form->input('SellProducts.product_id', array('multiple' => 'checkbox', 'options' => $products, 'div' => 'full-form', 'label' => false)); ?>

<?php echo $this->Form->end(array('label' => __('Save'), 'div' => 'center', 'class' => 'bouton-vert')); ?>

Answer 1

You have two options. Either let cakePHP do some magic with the hasAndBelongsToMany relationship or doing it manually which is necessary if you add attributes to the join table

1. CakePHP HABTM

Using the capabilities of CakePHP and making a straight forward solution I would make these changes:

Model

If one company has more than one product, and the products belong to many companies. It is a hasAndBelongsToMany relationship between Company<->Product

// company.php
...
var $hasAndBelongsToMany = array(
    'Product' => array(
        'className' => 'Product',
        'joinTable' => 'companies_products',
        'foreignKey' => 'company_id',
        'associationForeignKey' => 'product_id',
        'unique' => true,
    )
);
...
// similar in product.php 

Add the required table 'companies_products' in the database.

Controller

Then in the add function from the Company Controller there should be something like:

$products = $this->Company->Product->find('list');
$this->set(compact('products'));

View

Finally insert the products in the add.ctp, the select should allow multiple selections and let cakePHP do some magic, like this:

echo $this->Form->input('products', array( 
                         'label' => 'Products to buy (Ctr+multiple choice)' 
                          'type' => 'select', 
                      'multiple' => true,
                       ));

2. Manually

When the HABTM becomes more 'exotic' and includes some attributes like in your case 'buy' or 'sell' you need to do the manual way. This is in the Product Controller setting manually the fields before inserting them in the database. Something like:

foreach($availableProducts as $availableProduct ){
  $this->Product->ProductAvailabilities->create();
  $this->Product->ProductAvailabilities->set( array(
    'company_id' => $this->Product->id,
    'product_id' => $availableProduct['Product']['id'],
    'buys' => $availableProduct['Product']['buy'], 
    'sells' => $availableProduct['Product']['sell'] 
             // or however you send it to the controller
  ));
$this->Product->ProductAvailabilities->save();
}

Let's hope this helps you ...

Answer 2

you are planning a habtm-relationship (m:n) with the possibility to have extra fields in the join table. Even though this can be done with regular habtm I prefer the way you choose and implement the m:n as 1:n:1, which is simply the same and gives you more options when saving your data.

Here is a similar question and answer

As for your question: You can collect the the data from your products table like this:

$this->Company->ProductAvailability->Product->find('all', $params);

Also you might want to have a look at the containable-behaviour which is very useful for this use case:

$params['conditions'] = array(
    'Company.id' => $id
);
$params['contain'] => array(
    'ProductAvailability' => array(
        'conditions' =>array(
            'buys' => 1
        ),
        'Product' => array(
            'order' => array(
                'name' => 'ASC'
            )
        )
    )
);
$this->Company->find('all', $params);