Yii 怎么给 left join 加 and 条件? [ 1.1 版本 ]
public function actionIndex ()
{
parent::_acl();
$model = new user();
$criteria = new CDbCriteria();
$criteria->order = 't.id DESC';
$criteria->with = 'authentication';
$criteria->condition='authentication.status=2';
$count = $model->count($criteria);
$pages = new CPagination($count);
$pages->pageSize = 10;
$criteria->limit = $pages->pageSize;
$criteria->offset = $pages->currentPage * $pages->pageSize;
$result = $model->findAll($criteria);
$this->render('user_index', array ('datalist' => $result , 'pagebar' => $pages ));
}
上面这段代码sql语句是:
SELECT `t`.`id` AS `t0_c0`, `t`.`phone` AS `t0_c1`, `t`.`password` AS `t0_c2`, `t`.`paypass` AS `t0_c3`, `t`.`salt` AS `t0_c4`, `t`.`realname` AS `t0_c5`, `t`.`cerno` AS `t0_c6`, `t`.`regtime` AS `t0_c7`, `t`.`regip` AS `t0_c8`, `t`.`lastloginip` AS `t0_c9`, `t`.`lastlogintime` AS `t0_c10`, `t`.`status` AS `t0_c11`, `t`.`authentication` AS `t0_c12`, `authentication`.`id` AS `t1_c0`, `authentication`.`userid` AS `t1_c1`, `authentication`.`realname` AS `t1_c2`, `authentication`.`cerno` AS `t1_c3`, `authentication`.`cerpic1` AS `t1_c4`, `authentication`.`cerpic2` AS `t1_c5`, `authentication`.`cerpic3` AS `t1_c6`, `authentication`.`cardno` AS `t1_c7`, `authentication`.`cardpic1` AS `t1_c8`, `authentication`.`cardpic2` AS `t1_c9`, `authentication`.`applytime` AS `t1_c10`, `authentication`.`operator1` AS `t1_c11`, `authentication`.`operatetime1` AS `t1_c12`, `authentication`.`operator2` AS `t1_c13`, `authentication`.`operatetime2` AS `t1_c14`, `authentication`.`status` AS `t1_c15` FROM `yixin_user` `t` LEFT OUTER JOIN `yixin_user_authentication` `authentication` ON (`authentication`.`userid`=`t`.`id`) WHERE (authentication.status=2) ORDER BY t.id DESC LIMIT 10
想问下authentication.status=2
这句是怎么加and条件,而不是加到where条件里面?
peaceful 补充于 2015-03-16 12:33
这个网站搜索功能坏掉了,输入关键词,点搜索,一闪就没反应了
peaceful 补充于 2015-03-17 16:48
public function actionIndex ()
{
parent::_acl();
$model = new user();
$criteria = new CDbCriteria();
$criteria->order = 'user.id DESC';
$criteria->alias = 'user';
$criteria->join='LEFT JOIN user_authentication ON user.id=user_authentication.userid and user_authentication.status=2';
$count = $model->count($criteria);
$pages = new CPagination($count);
$pages->pageSize = 10;
$criteria->limit = $pages->pageSize;
$criteria->offset = $pages->currentPage * $pages->pageSize;
$result = $model->findAll($criteria);
$this->render('user_index', array ('datalist' => $result , 'pagebar' => $pages ));
}
这样用,对应的sql语句是:
SELECT `t`.`id` AS `t0_c0`, `t`.`phone` AS `t0_c1`, `t`.`password` AS `t0_c2`, `t`.`paypass` AS `t0_c3`, `t`.`salt` AS `t0_c4`, `t`.`realname` AS `t0_c5`, `t`.`cerno` AS `t0_c6`, `t`.`regtime` AS `t0_c7`, `t`.`regip` AS `t0_c8`, `t`.`lastloginip` AS `t0_c9`, `t`.`lastlogintime` AS `t0_c10`, `t`.`status` AS `t0_c11`, `t`.`authentication` AS `t0_c12`, `authentication`.`id` AS `t1_c0`, `authentication`.`userid` AS `t1_c1`, `authentication`.`realname` AS `t1_c2`, `authentication`.`cerno` AS `t1_c3`, `authentication`.`cerpic1` AS `t1_c4`, `authentication`.`cerpic2` AS `t1_c5`, `authentication`.`cerpic3` AS `t1_c6`, `authentication`.`cardno` AS `t1_c7`, `authentication`.`cardpic1` AS `t1_c8`, `authentication`.`cardpic2` AS `t1_c9`, `authentication`.`applytime` AS `t1_c10`, `authentication`.`operator1` AS `t1_c11`, `authentication`.`operatetime1` AS `t1_c12`, `authentication`.`operator2` AS `t1_c13`, `authentication`.`operatetime2` AS `t1_c14`, `authentication`.`status` AS `t1_c15` FROM `user` `t` LEFT OUTER JOIN `user_authentication` `authentication` ON (`authentication`.`userid`=`t`.`id`) WHERE (authentication.status=2) ORDER BY t.id DESC LIMIT 10)
共 1 个回答
peaceful
注册时间:2015-03-16
最后登录:2015-03-17
在线时长:2小时21分
最后登录:2015-03-17
在线时长:2小时21分
- 粉丝0
- 金钱25
- 威望0
- 积分45