2015-03-16 09:36:18 9452次浏览 1条回答 0 悬赏 5 金钱
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条件里面?

补充于 2015-03-16 12:33

这个网站搜索功能坏掉了,输入关键词,点搜索,一闪就没反应了

补充于 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)
  • 回答于 2015-03-16 17:32 举报

    这样?
    authentication.status=2 AND xx=xx...

    1 条回复
    回复于 2015-03-16 18:39 回复

    两个表user和authentication,想用user左联authentication,查询user表和对应authentication中status=2的记录,我用的$criteria->with = 'authentication';
    $criteria->condition='authentication.status=2';
    这样直接加到where ()里面了,我想把status=2 加到on 后面and条件,不要加到where里面,加where里面在authentication为空的时候,即使user表有记录,返回结果也是空的

您需要登录后才可以回答。登录 | 立即注册
peaceful
试用期

peaceful

注册时间:2015-03-16
最后登录:2015-03-17
在线时长:2小时21分
  • 粉丝0
  • 金钱25
  • 威望0
  • 积分45

热门问题