關(guān)于laravel 子查詢 & join的使用

更新時間：2019年10月16日 15:49:32 作者：rpdhao

今天小編就為大家分享一篇關(guān)于laravel 子查詢 & join的使用，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧

本項目中關(guān)聯(lián)了2個數(shù)據(jù)庫

'default' => env('DB_CONNECTION', 'mysql'), //默認(rèn)使用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快照表庫的關(guān)聯(lián)數(shù)據(jù)，從而實現(xiàn)以下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()) // 綁定參數(shù)，否則sql語句會只有'?'
  ->get();

而join語句中可傳入匿名函數(shù)重新構(gòu)造，如再其中加多幾個連接條件，或者查詢條件

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

實現(xiàn)上述需求完整代碼如下：

$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了一次，應(yīng)該可以再優(yōu)化一下.

以上這篇關(guān)于laravel 子查詢 & join的使用就是小編分享給大家的全部內(nèi)容了，希望能給大家一個參考，也希望大家多多支持腳本之家。

您可能感興趣的文章: