您好,登錄后才能下訂單哦!
這篇文章給大家分享的是有關(guān)laravel中子查詢&join的使用案例的內(nèi)容。小編覺得挺實(shí)用的,因此分享給大家做個(gè)參考,一起跟隨小編過來看看吧。
本項(xiàng)目中關(guān)聯(lián)了2個(gè)數(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, ], ],
在某個(gè)需求中,需要使用子查詢獲取snapshot快照表庫的關(guān)聯(lián)數(shù)據(jù),從而實(shí)現(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語句會(huì)只有'?' ->get();
而join語句中可傳入匿名函數(shù)重新構(gòu)造,如再其中加多幾個(gè)連接條件,或者查詢條件
$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'; })
實(shí)現(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)于“l(fā)aravel中子查詢&join的使用案例”這篇文章就分享到這里了,希望以上內(nèi)容可以對(duì)大家有一定的幫助,讓大家可以學(xué)到更多知識(shí),如果覺得文章不錯(cuò),可以把它分享出去讓更多的人看到吧!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。