Execute commands regularly every day, and synchronize the sum of a field value in one table in the command to another table
1. The current scenario is as follows. In one table, there is a field of payment method. Now if it is necessary, the payment method will be summarized into another table regularly. In order to prevent the records before the query is repeated every time the timing is executed, it is necessary to record a last execution time.
2. Decide to query all the non-repeatable payment methods first. If there is the last execution time, then add the conditions, and the creation time of the record is less than or equal to the last execution time.
/**
* 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
select
distinct `payment_type`
from
`table`
where
`table`.`created_at_gmt` < '2024-09-12 02:56:29'
4. Print out $paymenttypes, the result is in line with expectations.
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. Try to set the current time to 6 months ago, and the final code is implemented as follows
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;
}
The first time to execute the synchronization command:
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. Try to set the current time to 3 months ago, and execute the synchronization command for the second time, and the SQL executed is as follows. in line with expectations.
$now = Carbon::now()->subMonths(3);
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. Try to set the current time to now, and execute the synchronization command for the third time, and the executed SQL is as follows. in line with expectations. as shown in Figure 1
Figure 1
$now = Carbon::now();
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;
