cakephpcakephp-2.3

Retrieve all record with a custom condition from controller


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

I have 2 model classes, Item and 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:

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);