php获取递归关联的单号集合

获取递归关联的单号集合

1、需求

G原采购单
	A返修单(来自G\G1\S)
		AA返修单(来自A)
			AAA返修单(来自AA)
		AA2返修单(来自A)
			AA2A返修单(来自AA2)
G1原采购单
G2原采购单
	S返修单(来自G2)

• 通过AA2A，获取G\G1\G2\A\S\AA\AA2\AAA\AA2A
• 通过S，获取G\G1\G2\A\S\AA\AA2\AAA\AA2A

即通过表格任意一个单号都获取所有

2、mysql

• 建表

CREATE TABLE `relation` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `purchase_no` varchar(48) NOT NULL DEFAULT '' COMMENT '采购单号',
  `source_purchase_no` varchar(48) NOT NULL DEFAULT '' COMMENT '源采购单号',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_purchase_no` (`purchase_no`),
  KEY `idx_source_purchase_no` (`source_purchase_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='关系表';

• 数据

INSERT INTO `relation` (`id`, `purchase_no`, `source_purchase_no`) VALUES (1, 'A', 'G');
INSERT INTO `relation` (`id`, `purchase_no`, `source_purchase_no`) VALUES (2, 'A', 'G1');
INSERT INTO `relation` (`id`, `purchase_no`, `source_purchase_no`) VALUES (3, 'A', 'S');
INSERT INTO `relation` (`id`, `purchase_no`, `source_purchase_no`) VALUES (4, 'AA', 'A');
INSERT INTO `relation` (`id`, `purchase_no`, `source_purchase_no`) VALUES (5, 'AAA', 'AA');
INSERT INTO `relation` (`id`, `purchase_no`, `source_purchase_no`) VALUES (6, 'AA2', 'A');
INSERT INTO `relation` (`id`, `purchase_no`, `source_purchase_no`) VALUES (7, 'AA2A', 'AA2');
INSERT INTO `relation` (`id`, `purchase_no`, `source_purchase_no`) VALUES (8, 'S', 'G2');

id	purchase_no	source_purchase_no
1	A	G
2	A	G1
3	A	S
4	AA	A
5	AAA	AA
6	AA2	A
7	AA2A	AA2
8	S	G2

3、php

• 通过递归查询的方式实现

/**
     * 获取关系采购单号集合
     * @param string $purchaseNo
     * @return array|string[]
     */
    public function getRelationPurchaseNos(string $purchaseNo)
    {
        if (empty($purchaseNo)) {
            return [];
        }

        $searchNos = [$purchaseNo];
        $count = 10;
        while (
            ($relationR = Relation::whereIn('purchase_no', $searchNos)
                ->OrWhereIn('source_purchase_no', $searchNos)
                ->get()
                ->toArray()) && $count > 0
        ) {
            $count--;
            $purchaseNos = array_column($relationR, 'purchase_no');
            $sourcePurchaseNos = array_column($relationR, 'source_purchase_no');
            $tmpNos = array_unique(array_merge($purchaseNos, $sourcePurchaseNos));
            $tmpNos = array_diff($tmpNos, $searchNos);
            if (empty($tmpNos)) {
                break;
            }
            $searchNos = array_merge($tmpNos, $searchNos);
        }

        return $searchNos;
    }