关于laravel 子查询 & join的使用

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

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

?

1 2 3 4 5

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

?

1 2 3 4 5 6

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

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

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

原文链接：https://blog.csdn.net/rpdhao/article/details/72727354