关于laravel 子查询 & join的使用
2019-10-16 15:49:33
作者：rpdhao
今天小编就为大家分享一篇关于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
`tb_xx_snapshot`
WHERE
record_date BETWEEN '2017-05-17' AND '2017-05-23'
type = '1'
GROUP BY
game_room_id) as main
INNER JOIN `tb_xx_snapshot` AS `tb_gg` ON tb_gg.game_count = main.max_count and tb_gg.game_room_id = main.game_room_id
where
tb_gg.record_date BETWEEN '2017-05-17' AND '2017-05-23'
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的使用就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持脚本之家。
您可能感兴趣的文章:
php生成唯一uid的解决方法详解
2021-08-08
微信小程序结合ThinkPHP5授权登陆后获取手机号
2021-08-08
thinkphp6使用mysql悲观锁解决商品超卖问题的实现
2021-08-08
一次项目中Thinkphp绕过禁用函数的实战记录
2021-08-08
美国设下计谋,用娘炮文化重塑日本,已影响至中国
2021-11-19
时空伴随者是什么意思?时空伴随者介绍
2021-11-09
工信部称网盘企业免费用户最低速率应满足基本下载需求，天翼云盘回应：坚决支持，始终
2021-11-05
2022年放假安排出炉：五一连休5天 2022年所有节日一览表
2021-10-26
电脑版
-
返回首页
2006-2023 脚本之家 JB51.Net , All Rights Reserved.
