Wednesday, May 2, 2012

to realize a join query !

Hi all,

i trying to realize a join query but its not work and i can not see my problem/issue?!? I implement my code according to: http://book.cakephp.org/2.0/en/models/associations-linking-models-together.html

//Models:

class Category extends AppModel {

    public $hasMany = array(
        'Url' => array(
            'className'  => 'Url',
            'foreignKey' => 'category_id'
        )
    );
}

class Url extends AppModel {

    public $hasMany = array(
        'ATag' => array(
            'className'  => 'ATag',
            'foreignKey' => 'url_id'
        ),
        'HTag' => array(
            'className'  => 'HTag',
            'foreignKey' => 'url_id'
        ),
        'HeadTag' => array(
            'className'  => 'HeadTag',
            'foreignKey' => 'url_id'
        ),
        'PTag' => array(
            'className'  => 'PTag',
            'foreignKey' => 'url_id'
        ),
    );
}

//Controller:
    class FrontendController extends AppController {
       
        public function index(){
           
            $this->loadModel('Category');
           
            $keyword = 'some key';
            $domain  = 'www.some-domain.com'
           
            /*
              How can i realize this with joins:

              SELECT * FROM categories, urls, a_tags, head_tags
              WHERE categories.id = urls.category_id AND
              urls.id = a_tags.url_id AND
              urls.id = head_tags.url_id AND
              categories.name = 'some key' AND
              urls.name LIKE '%www.some-domain.com%'

            **/

            //it does not work(join)!
            $options['joins'] = array(
                array('table' => 'urls',
                    'alias' => 'Url',
                    'type' => 'LEFT',
                    'conditions' => array(
                        'Url.id = ATag.url_id',
                        'Url.id = HTag.url_id',
                        'Url.id = HeadTag.url_id',
                        'Url.id = PTag.url_id',
                    )
                )         
            );
           
            $options['conditions'] = array(
                        'AND' =>
                            array('Category.name' => $keyword),
                            array("Url.name LIKE" => "%$domain%"),
            );
            $this->Category->recursive = -1;
            $res = $this->Category->find('all', $options);
           
            pr($res)
            //Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'ATag.url_id' in 'on clause'
           /*
           SQL Query:
            SELECT `Category`.`id`, `Category`.`parent_id`, `Category`.`lft`, `Category`.`rght`, `Category`.`name`,
           `Category`.`created`,`Category`.`modified`,`Category`.`blacklist`, `Category`.`is_searched_by_user`
            FROM `ssxxxxx`.`categories` AS `Category`
            LEFT JOIN `ssxxxxx`.`urls` AS `Url` ON (
                   `Url`.`id` =`ATag`.`url_id` AND
                   `Url`.`id` = `HTag`.`url_id` AND
                   `Url`.`id` = `HeadTag`.`url_id` AND
                   `Url`.`id` = `PTag`.`url_id`)
            WHERE `Category`.`name` = 'some kategory' AND
            `Url`.`name` LIKE '%www.some-domain.com%'
           */

        }

No comments: