Month: November 2010

select multiple column from multiple table in cakePHP

$markers = $this->Post->find(‘all’, array( ‘joins’ => array(
array(
‘table’ => ‘cp_user’,
‘alias’ => ‘users’,
‘type’ => ‘left’,
‘foreignKey’ => false,
‘conditions’=> array(‘Post.postauthor = users.id’)
)
),
‘conditions’=>’Post.poststatus = 1’,
‘fields’=>array(‘Post.postid’,
‘Post.postdate’,
‘Post.posttitle’,
‘Post.postmessage’,
‘Post.poststatus’,
‘Post.commentstatus’,
‘Post.commenttotal’,
‘Users.id’,
‘Users.firstname’
)
)
);

$this->set(‘cp_post’,$markers);

This will generate the below query in mysql
—————————————————-
SELECT `Post`.`postid`, `Post`.`postdate`, `Post`.`posttitle`, `Post`.`postmessage`, `Post`.`poststatus`, `Post`.`commentstatus`, `Post`.`commenttotal`, `Users`.`id`, `Users`.`firstname` FROM `cp_posts` AS `Post` left JOIN cp_user AS `users` ON (`Post`.`postauthor` = `users`.`id`) WHERE `Post`.`poststatus` = 1