java分页子查询语句_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);