CakePHP加入内部

Question

I need to do the following query using the CakePHP find method:

SELECT * FROM `ads`
INNER JOIN fields_values ON fields_values.ref_id = ad.id 
WHERE ad.active = 1

I tried this function, but she doesn't work :

$ads = $this->Ad->find('all', array(
            'joins' => array(
                array(
                    'table' => 'fields_values',
                    'alias' => 'fv',
                    'type' => 'INNER',
                    'conditions' => array(
                        "Ad.id = fv.ref_id "
                    )
                )
            ),
            'conditions' => array(
                'Ad.active' => 1
            ),
        ));

Answer 1

your query is wrong it should be

SELECT * FROM `ads`
INNER JOIN fields_values as fv ON fv.ref_id = ads.id 
WHERE ads.active = 1

Now you can build it up with cake query. Which left join also does it.

$ads = $this->Ad->find('all', array(
            'conditions' => array(
                'Ad.active' => 1
            ),
            'joins' => array(
                array(
                    'table' => 'fields_values',
                    'alias' => 'fv',
                    'type' => 'LEFT',
                    'conditions' => array(
                        "fv.ref_id = Ad.id",
                        // or you can add the top condition here
                        //'Ad.active' => 1
                    )
                )
            ),
        ));