laravel5.3-数据库操作下的局部or条件与全局or条…

2018-06-22 05:26:18来源:未知 阅读 ()

新老客户大回馈,云服务器低至5折

当用户名不为空时

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存在,你的其它条件就会失效。

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

 

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:PDO、PDOStatement、PDOException

下一篇:php解决约瑟夫环