CODEWITHSUNDEEP
cakephpassociationsmodel-associations
Marvil Joy
Marvil Joy

Reputation: 662

Select records which have atleast one hasMany relation row in Cakephp

I'm facing a problem with cakephp associations in Models.

I have to Select records which have atleast one hasMany reation row

Model

class Category extends AppModel 
{  
    public $hasMany = array(
             'Product' => array(         
             'className' => 'Product',
             'foreignKey' => 'CategoryId',                
             )              
        );
    }

Query 

$categories = $this->Category->find('all');

I only needed the categories which have atleast one product entry

Categories Like : Shirts, Footwear, Glasses etc

Products like :

Small, medium, large (Shirts) With Frame, UV protected (Glass)

So, i jus want to get Shirts and Glasses Categories only because for the above example there is no products for Footwear

Upvotes: 0

Views: 1040

Answers (1)

ndm
ndm

Reputation: 60463

Use counterCache or joins

Please refer to CakePHP - Find and count how many associated records exist

The most simple way with the best performance would be using a properly indexed counter cache field as shown in the linked answer.

Sice the linked answer is not an exact duplicate with respect to the join, here's some additional info, instead of using HAVING COUNT with the join you'd use a IS NOT NULL condition. Here's an (untested) example:

$this->Category->find('all', array(
    'joins' => array(
        array(
            'table' => 'products',
            'alias' => 'Product',
            'type' => 'LEFT',
            'conditions' => array('Category.id = Product.CategoryId')
        )
    ),
    'conditions' => array(
        'Product.CategoryId IS NOT NULL'
    )
    'group' => 'Category.id'
));

Depending on the used DBMS and version you might get better performance using an inner join:

$this->Category->find('all', array(
    'joins' => array(
        array(
            'table' => 'products',
            'alias' => 'Product',
            'type' => 'INNER',
            'conditions' => array('Category.id = Product.CategoryId')
        )
    ),
    'group' => 'Category.id'
));

Upvotes: 3

Related Questions