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

码农天地 - 2020-12-10 13:05:57

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);

特别申明：本文内容来源网络，版权归原作者所有，如有侵权请立即与我们联系（cy198701067573@163.com），我们将及时处理。