How to find data through foreign key in cakephp?

Question

 Product        Plan            ProductPlan
 id |name       id |name        id  | product_id | plan_id     
 1    aplha      1   a          1        1          2             
 2    bravo      2   b
 3    charlie 

I want to find Product name and Plan name against ProductPlan, if product id and plan id for product exist in ProductPlan then the name of plan and product will show, I tried a lot, the relation b/w tables are correct but i didn't get the exact data, query which i used that is

        $p_plan = $this->ProductPlan->find('all',array(
                                    'conditions'=>array(
                                                'ProductPlan.product_id'=>'Product.product_id'
                                                )                       
                                        )
                                    );  
    $this->set('p_plan', $p_plan);  

if some body help me , i'll very thaksful to him. Thanks in advance.

Relation For Plan

class Plan extends AppModel{ 

public $primaryKey='plan_id';

public $hasMany = array(
    'ProductPlan'=>array(
        'className'    => 'ProductPlan',
        'foreignKey'   => 'plan_id' 
    )
);

Product

class Product extends AppModel{ 

public $primaryKey='product_id';

public $hasMany = array(
    'ProductsUser'=>array(
        'className'    => 'ProductsUser',
        'foreignKey'   => 'product_id'  
    ),
    'ProductsUserNode'=>array(
        'className'    => 'ProductsUserNode',
        'foreignKey'   => 'product_id'  
    ),
    'ProductPlan'=>array(
        'className'    => 'ProductPlan',
        'foreignKey'   => 'product_id'  
    )
);

for Product Plan

class ProductPlan extends AppModel{
var $primaryKey='product_plan_id';
 public $belongsTo = array(
    'Product' => array(
        'className'    => 'Product',
        'foreignKey'   => 'product_id'
    ),
    'Plan' => array(
        'className'    => 'Plan',
        'foreignKey'   => 'plan_id'
    )       

     );
public $hasMany = array(
    'ProductPlansUserNode'=>array(
        'className'    => 'ProductPlansUserNode',
        'foreignKey'   => 'product_plan_id' 
    ),
);

}

Cystine

Answer 1

You should simply be able to use 'contain':-

$p_plan = $this->Product->find('all', array(
    'contain' => array('Plan')
));

This will return all your products, with their associated plans. Your Product and Plan models need a hasAndBelongToMany relationship. There is no need to define a model for your joins table.

class Product AppModel {
    ...
    public $hasAndBelongsToMany = array('Plan');
    ...
}

class Plan AppModel {
    ...
    public $hasAndBelongsToMany = array('Product');
    ...
}

As a side note I'd personally avoid overriding CakePHP's default way of handling primary keys. It is better to stick with convention and use id.

Seaton

i have used it, but the product is repeating, for example if product (alpha) has two plan (a and b), then the aplha repeated it self. – Cystine

i want that if product is one and plan is many against the plan so it written as first product then product against plan – Cystine

Not sure I follow you. Do you mean you want all your products returning with their associated plans? Something like $this->Product->find('all', array('contain'=>array('Plan')). – Seaton

but i think the product and plan comes from productplan table, because the product_id and Plan_id are in ProductPlan table. – Cystine

Your ProductPlan should be a join table so your Product and Plan models should use hasAndBelongsToMany relationships to one another. You don't need to directly use the join table. – Seaton

i have define the relation b/w these table above in my code now, would u think is right? – Cystine

for this i need to create a new table and modal which name will plans_products and modal plansproduct, for this i will also define the relation of PlansProduct table. – Cystine