hyperf 使用模型写较为复杂 union 子查询并做分页


hyperf 使用模型写 union 子查询并做分页

最终需要实现的 sql 语句为如下所示:


SELECT
	`dfo_al`.* 
FROM
	(
	(
SELECT
	`dfo_account_log`.`log_id`,
	`dfo_account_log`.`change_time`,
	`dfo_account_log`.`user_id`,
	`dfo_account_log`.`pay_points`,
	`dfo_account_log`.`change_type`,
	`dfo_account_log`.`from_user_id`,
	`dfo_u`.`user_id` AS `u_user_id`,
	`dfo_u`.`username`,
	`dfo_u`.`head_pic`,
	`dfo_u`.`vip_time` 
FROM
	`dfo_account_log`
	LEFT JOIN `dfo_users` AS `dfo_u` ON `dfo_account_log`.`user_id` = `dfo_u`.`user_id` 
WHERE
	( `dfo_account_log`.`user_id` = 3649 AND `dfo_account_log`.`pay_points` > 0 ) 
	AND ( `dfo_account_log`.`from_user_id` IS NULL OR `dfo_account_log`.`from_user_id` = 0 ) 
	) UNION
	(
SELECT
	`dfo_account_log`.`log_id`,
	`dfo_account_log`.`change_time`,
	`dfo_account_log`.`user_id`,
	`dfo_account_log`.`pay_points`,
	`dfo_account_log`.`change_type`,
	`dfo_account_log`.`from_user_id`,
	`dfo_u`.`user_id` AS `u_user_id`,
	`dfo_u`.`username`,
	`dfo_u`.`head_pic`,
	`dfo_u`.`vip_time` 
FROM
	`dfo_account_log`
	LEFT JOIN `dfo_users` AS `dfo_u` ON `dfo_account_log`.`from_user_id` = `dfo_u`.`user_id` 
WHERE
	( `dfo_account_log`.`user_id` = 3649 AND `dfo_account_log`.`pay_points` > 0 ) 
	AND ( `dfo_account_log`.`from_user_id` IS NOT NULL OR `dfo_account_log`.`from_user_id` > 0 ) 
	) 
	) AS dfo_al 
ORDER BY
	`log_id` DESC 
	LIMIT 2 OFFSET 0

hyperf 代码为

以下代码仅为示例代码,仅提供参考使用,具体实现请根据你自己的业务逻辑实现。


$tbl = 'account_log';
$where = [
    [$tbl . '.user_id', '=', auth()->user_id],
    [$tbl . '.pay_points', '>', 0]
];
$fields = [
    $tbl . '.log_id',
    $tbl . '.change_time',
    $tbl . '.user_id',
    $tbl . '.pay_points',
    $tbl . '.change_type',
    $tbl . '.from_user_id',
    'u.user_id AS u_user_id',
    'u.username',
    'u.head_pic',
    'u.vip_time'
];

$table1 = $this->accountLogModel
    ->leftJoin('users as u', $tbl . '.user_id', '=', 'u.user_id')
    ->select($fields)
    ->where($where)
    ->where(function ($query) use ($tbl) {
        $query->whereNull($tbl . '.from_user_id')->orWhere($tbl . '.from_user_id', 0);
    });

$table2 = $this->accountLogModel
    ->leftJoin('users as u', $tbl . '.from_user_id', '=', 'u.user_id')
    ->select($fields)
    ->where($where)
    ->where(function ($query) use ($tbl) {
        $query->whereNotNull($tbl . '.from_user_id')->orWhere($tbl . '.from_user_id', '>', 0);
    });

$table = $table1->union($table2);

$tablePrefix = Db::connection()->getTablePrefix();  // 获取数据表前缀 => 或者使用  $tablePrefix = Db::getConfig('prefix'); 都可以
$model = $this->accountLogModel
    ->mergeBindings($table->getQuery())
    ->select(['al.*'])
    ->from(Db::raw("({$table->toSql()}) as {$tablePrefix}" . 'al'));

return $model->orderBy('log_id', 'desc')->paginate(2);

文章作者: Alex
版权声明: 本博客所有文章除特別声明外,均采用 CC BY 4.0 许可协议。转载请注明来源 Alex !
评论
 上一篇
下一篇 
针对 hyperf 框架实现简单限流器,类似 laravel 框架 throttle 中间件功能 针对 hyperf 框架实现简单限流器,类似 laravel 框架 throttle 中间件功能
所谓限流器,指的是限制访问指定服务/路由的流量,通俗点说,就是限制单位时间内访问指定服务/路由的次数(频率),从系统架构角度看,通过限流器可以有效避免短时间内的异常高并发请求导致系统负载过高,从而达到保护系统的目的,
2021-06-10
  目录