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%'
*/
}
--
Our newest site for the community: CakePHP Video Tutorials
http://tv.cakephp.org
Check out the new CakePHP Questions site http://ask.cakephp.org and help others
with their CakePHP related questions.
To unsubscribe from this group, send email to
[email protected] For more options, visit this group at
http://groups.google.com/group/cake-php
