[FIXED] Retrieve all record with a custom condition from controller

Issue

I have a simple question, but I'm stuck!

I have 2 model classes, Item and Typology.

• Typology belongs to Item
• Item has many Typology

Tables are like this.

TYPOLOGY: id | item_id | title | description | published
ITEM:     id | title | price | created | published

So what I want to retrieve is:

• find all typologies WHERE published = 1 and item to which they belong has published = 1.

In the TypologyControllers I have written this code:

$typologies = $this->Typology->find(
'all', 
array(
'contain' => array(
'Item' => array(
'conditions' => array(
'Item.published =' => "1"))), 
'conditions' => array(
'Typology.published'=>'1'), 
'recursive' => -1, 
'order' => array(
'Typology.' . $this->Typology->primaryKey . ' DESC')
));


    $this->set('typologies', $typologies);  

Solution

why you dont use a joins:

$typologies = $this->Typology->find('all', array(
         'joins' => array(
            array(
                'table' => 'items',
                'alias' => 'Item',
                'type' => 'LEFT',
                'conditions' => array(
                'Item.id = Typology.item_id'
                )
            )
        ),
            'conditions' => array(
            'Item.published' => 1, 
            'Typology.published' => 1 
        ),
            'order' => array(
            'Typology.id' => 'DESC'
        ),
            'fields' => array('Item.*', 'Typology.*'),
            'recursive' => -1
        ));


    $this->set('typologies', $typologies); 

Answered By - CoolLife