需求

cardealers 经销商表,
testdrive 试驾表,
查找试驾数超过100的经销商。显示账号信息和经销商名

思考SQL

以经销商表为主表,管理试驾表和会员表
以经销商表的id分组,分组之后可以查出每组经销商的试驾数。
然后having 选 试驾超过100的经销商

原生sql

  1. SELECT
  2. member.fullName,
  3. cardealers.id,
  4. cardealers.carShop,
  5. count(*) AS countT
  6. FROM
  7. cardealers
  8. LEFT JOIN testdrive ON testdrive.carDealersId = cardealers.id
  9. LEFT JOIN member ON member.id = cardealers.memberId
  10. WHERE
  11. source = 'normalDrive' #试驾类型是普通试驾
  12. AND approve = 'approved' #审核通过的试驾
  13. AND reward = 0 #未发奖的
  14. GROUP BY
  15. cardealers.id
  16. HAVING
  17. countT > 100
  18. ORDER BY
  19. countT DESC
  20. LIMIT 0,2

image.png

laravel 中SQL写法

laravel having中分页获取数据不能函数统计的别名,需要使用havingraw(函数)
orderby可以使用函数统计的别名

<?php
$cardealer = DB::table('cardealers')
  ->leftJoin('testdrive', 'testdrive.carDealersId', '=', 'cardealers.id')
  ->leftJoin('Member', 'cardealers.memberId', '=', 'Member.id')
  ->where('source','normalDrive')
  ->where('approve','approved');

if($model->reward==1){
  $cardealer = $cardealer->where('reward', '>', 0);
}else{
  $cardealer = $cardealer->where('reward', '=', 0);
}
$cardealer = $cardealer
  ->select('cardealers.*','Member.fullName', 'Member.username',DB::raw('count(cardealers.id) as countT'))
  ->groupBy('cardealers.id')
  // ->having('countT','>',$model->rewardCount) //get()可以获取所有结果,但是分页获取报错,原因是laravel这里having不能使用别名,将别名改为ardealers.id是可以的。所以认为是别名的原因
  // ->havingRaw('countT > 2') //分页报错:这里不能使用别名 countT
  ->havingRaw('count(cardealers.id) >= '.$model->rewardCount) //可以,havingraw(括号里只有一对单引号)
  ->OrderBy('countT','desc') //这里可以使用别名
  // ->get();
  ->paginate(10);
return $cardealer;