1. The current scenario is as follows: one table has a payment method field, and we now need to periodically aggregate the payment methods into another table. To prevent querying previous records repeatedly during each scheduled execution, we need to record the last execution time.
2. I decided to first query all unique payment methods. If a last execution time exists, I will add a condition so that the record’s creation time is less than or equal to the last execution time.
PHP
/**
* Execute the console command.
*
* @return mixed
*/
public function handle()
{
$startTime = time();
$this->line('同步付款方式到选项');
$now = Carbon::now();
// 上一次的执行时间
// $lastTime = Carbon::now();
$builder = Model::query()
->select('payment_type')
->where('table.created_at_gmt', '<', $now)
->distinct();
if (!empty($lastTime)) {
$builder->where('table.created_at_gmt', '>=', $now);
}
$paymentTypes = $builder->pluck('payment_type')->filter()->unique()->toArray();
print_r($paymentTypes);exit;
$endTime = time();
Log::info("同步付款方式到选项; time:" . Carbon::now('GMT+8')->toDateTimeString() . ' 耗时：' . $endTime - $startTime . '秒');
$this->line('同步完成' . ' 耗时：' . $endTime - $startTime . '秒');
return Command::SUCCESS;
}
3. View the generated SQL.
SQL
select
distinct `payment_type`
from
`table`
where
`table`.`created_at_gmt` < '2024-09-12 02:56:29'
4. Print out $paymentTypes; the result meets expectations.
Plaintext
Array
(
[1] => PP
[2] => credit
[3] => payssion
[4] => 网上支付
[5] => 1
[6] => 21
[7] => paypal_cluster_rest
[8] => asiabill
[9] => nspayment_local
[10] => worldpay
[11] => paypal_rest
[12] => srpay
[13] => nspayment
[14] => 1000
[15] => worldpay_googlepay
)
5. First, I tried setting the current time to 6 months ago. The final code implementation is as follows:
PHP
private string $cachedLastTimeKey = 'table:sync-payment-type-to-option:last_time';
/**
* Execute the console command.
*
* @return mixed
*/
public function handle()
{
$startTime = time();
$this->line('同步付款方式到选项');
$now = Carbon::now()->subMonths(3);
$optionRepository = app(IOptionRepository::class);
$title = 'payment_types';
// 选项中的付款方式
$optionPaymentType = $optionRepository->fetchBy(['title' => $title]);
// 上一次的执行时间
$lastTime = Cache::store('redis')->get($this->cachedLastTimeKey);
$builder = Model::query()
->select('payment_type')
->where('table.created_at_gmt', '<', $now)
->distinct();
if (isset($lastTime)) {
$builder->where('table.created_at_gmt', '>=', $lastTime);
}
$paymentTypes = $builder->pluck('payment_type')->filter()->unique()->values()->all();
if (!empty($optionPaymentType)) {
// 获取两个数组的并集
$optionPaymentTypes = array_values(array_unique(array_merge($optionPaymentType->value, $paymentTypes)));
} else {
$optionPaymentTypes = $paymentTypes;
}
$optionRepository->updateOrCreateOptionBy(['title' => $title], ['value' => $optionPaymentTypes]);
Cache::store('redis')->put($this->cachedLastTimeKey, $now->toDateTimeString());
$endTime = time();
Log::info("同步付款方式到选项; time:" . Carbon::now('GMT+8')->toDateTimeString() . ' 耗时：' . $endTime - $startTime . '秒');
$this->line('同步完成' . ' 耗时：' . $endTime - $startTime . '秒');
return Command::SUCCESS;
}
Execute the sync command for the 1st time:
SQL
select
*
from
`options`
where
`title` = 'payment_types'
limit
1;
select
distinct `payment_type`
from
`table`
where
`table`.`created_at_gmt` < '2024-03-12 05:59:02';
select
*
from
`options`
where
`title` = 'payment_types'
limit
1;
insert into
`options` (`title`, `value`)
values
(
'payment_type',
'[\"PP\",\"credit\",\"payssion\",\"\u7f51\u4e0a\u652f\u4ed8\",\"1\",\"21\",\"paypal_cluster_rest\",\"asiabill\",\"nspayment_local\",\"worldpay\",\"paypal_rest\",\"srpay\",\"nspayment\",\"1000\",\"worldpay_googlepay\"]'
);
6. Next, I tried setting the current time to 3 months ago and executed the sync command for the 2nd time. The executed SQL is as follows. It meets expectations.
PHP
$now = Carbon::now()->subMonths(3);
SQL
select
*
from
`options`
where
`title` = 'payment_types'
limit
1;
select
distinct `payment_type`
from
`table`
where
`table`.`created_at_gmt` < '2024-06-12 06:00:50'
and `table`.`created_at_gmt` >= '2024-03-12 05:59:02';
select
*
from
`options`
where
`title` = 'payment_types'
limit
1;
update
`options`
set
`value` = '[\"PP\",\"credit\",\"payssion\",\"\u7f51\u4e0a\u652f\u4ed8\",\"1\",\"21\",\"paypal_cluster_rest\",\"asiabill\",\"nspayment_local\",\"worldpay\",\"paypal_rest\",\"srpay\",\"nspayment\",\"1000\",\"worldpay_googlepay\"]'
where
`id` = 75;
6. Then I tried setting the current time to now and executed the sync command for the 3rd time. The executed SQL is as follows. It meets expectations. See Figure 1.
PHP
$now = Carbon::now();
SQL
select
*
from
`options`
where
`title` = 'payment_types'
limit
1;
select
distinct `payment_type`
from
`table`
where
`table`.`created_at_gmt` < '2024-09-12 06:04:35'
and `table`.`created_at_gmt` >= '2024-06-12 06:00:50';
select
*
from
`options`
where
`title` = 'payment_types'
limit
1;
update
`options`
set
`value` = '[\"PP\",\"credit\",\"payssion\",\"\u7f51\u4e0a\u652f\u4ed8\",\"1\",\"21\",\"paypal_cluster_rest\",\"asiabill\",\"nspayment_local\",\"worldpay\",\"paypal_rest\",\"srpay\",\"nspayment\",\"1000\",\"worldpay_googlepay\",\"paypal\",\"stripe\"]'
where
`id` = 75;