CakePHP, don't know how to select category name from table product and category

Question

I am very new in cakephp I have two table tb_product and tb_category I want to select like sql below. How can I do it with cakephp?

SQL:

SELECT tb_product.id, tb_product.name, tb_category.name 
FROM tb_product 
INNER JOIN tb_category 
WHERE tb_product.cat_id = tb_category.cat_id"

Thank you all helper!

tb_product:
----------
id
name
===========

tb_category:
-----------
cat_id
name
==========

Thank you in advanced !!!

Answer 1

You can either create a model association in your Cake model for Product to automatically join to Category in a hasOne relationship based on the cat_id foreign key, or you can do it with your find() query as a manual join:

$results = $this->Product->find('all', array(
    'joins' => array(
        array(
            'table' => 'tb_category',
            'alias' => 'Category',
            'type' => 'INNER',
            'conditions' => 'Category.cat_id = Product.cat_id'
        )
    ),
    'fields' => array(
        'Product.id',
        'Product.name',
        'Category.name'
    )
));

---

A model association would look something like this:

class Product extends AppModel {
    // ...
    public $hasOne = array(
        'Category' => array('foreignKey' => 'cat_id')
    );
}

Then when you query your product model, categories that match should be returned with it:

$results = $this->Product->find('all');