您的位置:首页 > 教程 > PHP编程 > 关于laravel 子查询 & join的使用

关于laravel 子查询 & join的使用

2020-09-04 07:57:24 来源:易采站长站 作者:

关于laravel 子查询 & join的使用,语句,中子,加多,条件,需求

关于laravel 子查询 & join的使用

易采站长站,站长之家为您整理了关于laravel 子查询 & join的使用的相关内容。

本项目中关联了2个数据库

'default' => env('DB_CONNECTION', 'mysql'), //默认使用mysql为连接库'connections' => [  'mysql' => [   'driver' => 'mysql',   'host'  => '192.168.0.xx',   'database' => 'database',   'username' => 'root',   'password' => '',   'charset' => 'utf8',   'collation' => 'utf8_unicode_ci',   'prefix' => 'tb_',   'strict' => false,  ],  'mysql_snapshot' => [   'driver' => 'mysql',   'host'  => env('DB_HOST_SNAPSHOT', '192.168.0.xx'),   'database' => env('DB_DATABASE_SNAPSHOT', 'snapshot'),   'username' => env('DB_USERNAME_SNAPSHOT', 'root'),   'password' => env('DB_PASSWORD_SNAPSHOT', ''),   'charset' => 'utf8',   'collation' => 'utf8_unicode_ci',   'prefix' => 'tb_',   'strict' => false,  ], ],

在某个需求中,需要使用子查询获取snapshot快照表库的关联数据,从而实现以下sql逻辑

SELECT ... From (SELECT  sum(game_count) AS sum_count,  max(game_count) AS max_count,  game_room_id,  record_date FROM  `tb_xx_snapshot`  WHERE  record_date BETWEEN '2017-05-17' AND '2017-05-23'  AND   type = '1'  GROUP BY   game_room_id) as mainINNER JOIN `tb_xx_snapshot` AS `tb_gg` ON tb_gg.game_count = main.max_count and tb_gg.game_room_id = main.game_room_idwhere tb_gg.record_date BETWEEN '2017-05-17' AND '2017-05-23'AND  tb_gg.type = '1'GROUP BY  tb_gg.game_room_id;

其中子查询主要用到以下query builder语句

$query = DB::table('xx_snapshot')->where('xx','yy')->groupBy('xx');$main = DB::connection('mysql_snapshot')  ->table(DB::raw("({$query->toSql()}) as tb_main"))  ->mergeBindings($query->getQuery()) // 绑定参数,否则sql语句会只有'?'  ->get();

而join语句中可传入匿名函数重新构造,如再其中加多几个连接条件,或者查询条件

$con = DB::table('xx_snapshot')  ->join('xx_snapshot as gg', function ($query) {    $query->on('gg.game_count', '=', 'xx_snapshot.max_count')     ->on('gg.game_room_id', '=', 'xx_snapshot.game_room_id')     ->where('gg.xx','123';   })

实现上述需求完整代码如下:

$subQuery= GameroomModel::select(DB::raw('sum(game_count) as sum_count,max(game_count) as max_count,record_date,game_room_id'))       ->whereBetween('record_date',[$beginDay,$endDay])    ->where('type','1')    ->groupBy('game_room_id');$main = DB::connection('mysql_snapshot')  ->table(DB::raw("({$subQuery->toSql()}) as tb_main"))  ->mergeBindings($playerGame->getQuery())  ->join('gameroom_snapshot as gg', function ($join) {   $join->on('gg.game_count', '=', 'main.max_count')     ->on('gg.game_room_id', '=', 'main.game_room_id');  })  - >select('main.max_count','main.sum_count','gg.record_date','main.game_room_id')  ->whereBetween('gg.record_date',[$beginDay,$endDay])  ->groupBy('main.game_room_id')  ->get();

代码中子查询和外层都group by了一次,应该可以再优化一下.

以上就是关于对关于laravel 子查询 & join的使用的详细介绍。欢迎大家对关于laravel 子查询 & join的使用内容提出宝贵意见

微信扫一扫

易采站长站微信账号