php laravel 连接查询,关于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 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'

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的使用就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持脚本之家。