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


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


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

hyperf 代码为:


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

        $table1 = AccountLog::query()
            ->from('account_log as a')
            ->leftJoin('users as u', 'a.user_id', '=', 'u.user_id')
            ->select($fields)
            ->where($where)
            ->where(function ($query) {
                $query->whereNull('a.from_user_id')->orWhere('a.from_user_id', 0);
            });

        $table2 = AccountLog::query()
            ->from('account_log as a')
            ->leftJoin('users as u', 'a.from_user_id', '=', 'u.user_id')
            ->select($fields)
            ->where($where)
            ->where(function ($query) {
                $query->whereNotNull('a.from_user_id')->orWhere('a.from_user_id', '>', 0);
            });

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

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

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


Vote Vote Cancel Collect Collect Cancel

<< 上一篇: 完全不一样的 LaravelAdmin —— Windows Docker 搭建篇

>> 下一篇: Leetcode PHP题解--D133 9. Palindrome Number