正文  软件开发 > PHP编程技术 >

laravel5.3-数据库操作下的局部or条件与全局or条件(orWhere的局部与全局),laravel5.3-orwhere

laravel5.3-数据库操作下的局部or条件与全局or条件(orWhere的局部与全局),laravel5.3-orwhere当用户名不为空时 SELECT * FROM `ACCOUNT_R...

laravel5.3-数据库操作下的局部or条件与全局or条件(orWhere的局部与全局),laravel5.3-orwhere

当用户名不为空时

SELECT * FROM `ACCOUNT_RECHARGE` LEFT JOIN `ORDER` ON `ACCOUNT_RECHARGE`.`OrderNo` = `ORDER`.`OrderNo` LEFT JOIN `USER_ACCOUNT` ON `ORDER`.`UserId` = `USER_ACCOUNT`.`UserId` WHERE (`USER_ACCOUNT`.`UserName` LIKE ‘%18%‘ OR (`USER_ACCOUNT`.`MobileNo` LIKE ‘%18%‘) OR (`USER_ACCOUNT`.`EMail` LIKE ‘%18%‘)) AND `ACCOUNT_RECHARGE`.`OrderTime` BETWEEN ‘2017-08-11 0:0:0‘ AND ‘2017-08-11 23:59:59‘ ORDER BY `ACCOUNT_RECHARGE`.`OrderTime` DESC;

上述SQL语句在laravel5.3中的一种写法:

$model = DB::connection(‘Order_User‘)->table(‘ACCOUNT_RECHARGE‘)

                 ->leftJoin(‘ORDER‘, ‘ACCOUNT_RECHARGE.OrderNo‘, ‘=‘, ‘ORDER.OrderNo‘)

                 ->leftJoin(‘USER_ACCOUNT‘, ‘ORDER.UserId‘, ‘=‘, ‘USER_ACCOUNT.UserId‘)
                 
->select(‘ACCOUNT_RECHARGE.*‘, ‘USER_ACCOUNT.UserName‘, ‘USER_ACCOUNT.MobileNo‘, ‘USER_ACCOUNT.EMail‘)

                 ->orderBy(‘ACCOUNT_RECHARGE.OrderTime‘, ‘DESC‘);
if (!empty($username)) {
$username = trim($username);
$model = $model->where(function($query) use ($username) {
  $query->where(‘USER_ACCOUNT.UserName‘, ‘like‘, "%$username%")
     ->orWhere(function($query) use ($username) {
        $query->where(‘USER_ACCOUNT.MobileNo‘, ‘like‘, "%$username%");
      });
     ->orWhere(function($query) use ($username) {
        $query->where(‘USER_ACCOUNT.EMail‘, ‘like‘, "%$username%");
      });
  });
}

注意:在username不为空的判断里面,若没有使用use则会报未定义的变量username

对比

第一种

$model = $model->where(function($query) use ($username) {
  $query->where(‘USER_ACCOUNT.UserName‘, ‘like‘, "%$username%")
     ->orWhere(function($query) use ($username) {
        $query->where(‘USER_ACCOUNT.MobileNo‘, ‘like‘, "%$username%");
      });
     ->orWhere(function($query) use ($username) {
        $query->where(‘USER_ACCOUNT.EMail‘, ‘like‘, "%$username%");
      });
  });
}

第二种

$model = $model >where(‘USER_ACCOUNT.UserName‘, ‘like‘, "%$username%")->orWhere(‘MobileNo‘, ‘like‘, "%$username%")->orWhere
(‘EMail‘, ‘like‘, "%$username%");
}

的区别

第一种是局部的or条件,只是对username的or判断,而与其它条件是并且的关系,对照刚开始出现的SQL语句

而第二种是全局的or条件,若把第二种的语句替换掉第一种的语句,相当于SQL语句

SELECT * FROM `ACCOUNT_RECHARGE` LEFT JOIN `ORDER` ON `ACCOUNT_RECHARGE`.`OrderNo` = `ORDER`.`OrderNo` LEFT JOIN `USER_ACCOUNT` ON `ORDER`.`UserId` = `USER_ACCOUNT`.`UserId` WHERE `USER_ACCOUNT`.`UserName` LIKE ‘%18%‘ OR (`USER_ACCOUNT`.`MobileNo` LIKE ‘%18%‘) OR (`USER_ACCOUNT`.`EMail` LIKE ‘%18%‘) AND `ACCOUNT_RECHARGE`.`OrderTime` BETWEEN ‘2017-08-11 0:0:0‘ AND ‘2017-08-11 23:59:59‘ ORDER BY `ACCOUNT_RECHARGE`.`OrderTime` DESC;

这时,你会发现,只要username存在,你的其它条件就会失效。

这是我跳过的坑......

http://www.bkjia.com/PHPjc/1223136.htmlwww.bkjia.comtruehttp://www.bkjia.com/PHPjc/1223136.htmlTechArticlelaravel5.3-数据库操作下的局部or条件与全局or条件(orWhere的局部与全局),laravel5.3-orwhere 当用户名不为空时 SELECT * FROM `ACCOUNT_RECHARGE` LEFT J...