关于laravel 子查询 & join的使用

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

代码语言：javascript复制

'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逻辑

代码语言：javascript复制

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语句

代码语言：javascript复制

$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语句中可传入匿名函数重新构造，如再其中加多几个连接条件，或者查询条件

代码语言：javascript复制

$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';
   })

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

代码语言：javascript复制

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

sql laravel builder join snapshot

0 人点赞