每天定时执行命令，在命令中同步一张表中的某个字段值的汇总至另一张表中

1、现在场景如下，在一张表中，有一个付款方式的字段，现在有需要，将付款方式定时汇总至另一张表中。为了防止每次定时执行时，重复查询之前的记录，所以需要记录一个上次的执行时间。

2、决定先查询出所有的不重复的付款方式，如果存在上次的执行时间，则加入条件，记录的创建时间小于等于上次的执行时间。

    /**
     * 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、查看生成的 SQL

select
  distinct `payment_type`
from
  `table`
where
  `table`.`created_at_gmt` < '2024-09-12 02:56:29'

4、打印出 $paymentTypes，结果符合预期。

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、先尝试设置当前时间为 6 个月前，最终代码实现如下

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

第1次执行同步命令：

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、再尝试设置当前时间为 3 个月前，第2次执行同步命令，执行的 SQL 如下。符合预期。

$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、再尝试设置当前时间为现在，第3次执行同步命令，执行的 SQL 如下。符合预期。如图1

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