CTE를 사용하지 않고 SQL 쿼리에서 계층적 결과(pedigree)를 생성하는 방법

CTE를 사용하지 않고 SQL 쿼리에서 계층적 결과(pedigree)를 생성하는 방법

나는 개 데이터베이스를 가지고 있다.각각의 개는 시어와 댐의 부모가 있다.phpmyadmin과 MariaDB 10.0의 문제로 CTE를 사용할 수 없습니다.

CTE를 사용하기 위해 MariaDB 10.2.20으로 업데이트했습니다.아직 "인식할 수 없는 문 유형"을 수신하고 있습니다.phpMyAdmin의 (WID 근처)

테이블은animal

작업 중인 열은 다음과 같습니다.id,akc_reg_num,akc_parent_sire,akc_parent_dam

하위 카테고리를 로드하는 것과 마찬가지로 한 세대만 얻으려고 했지만, 부모가 두 명일 때는 한 세대뿐입니다.

나는 이 해결책을 시도해 보았지만, 각 형제자매의 두 부모에 대해 이해할 수 없다.SQL과의 테이블 부모 자식 관계를 사용하여 계층 트리의 1세대만 선택하려면 어떻게 해야 합니까?

표 예

CREATE TABLE  `animal` ( 
    `id` INT(11) NOT NULL AUTO_INCREMENT ,
     `akc_reg_num` VARCHAR(20) NOT NULL ,
     `akc_parent_sire` VARCHAR(20) NOT NULL ,
     `akc_parent_dam` VARCHAR(20) NOT NULL ,
     PRIMARY KEY (`id`)
) ENGINE = MyISAM;

INSERT INTO `animal` (`id`, `akc_reg_num`, `akc_parent_sire`, `akc_parent_dam`) VALUES
(NULL, '1', '2', '3'), 
(NULL, '2', '5', '6'), 
(NULL, '3', '9', ''), 
(NULL, '5', '', ''), 
(NULL, '6', '7', '8'), 
(NULL, '7', '', ''), 
(NULL, '8', '', ''), 
(NULL, '9', '10', '11'), 
(NULL, '10', '', ''), 
(NULL, '11', '12', ''), 
(NULL, '12', '', '');

코드:

include_once("db_conx.php");   

function getPedigree($node) { 
    // look up the parent of this node  
    $sql =  'SELECT akc_parent_sire, akc_parent_dam FROM animals WHERE akc_reg_num="'.$node.'";';
    $query = $db->prepare($sql);          
    $query->execute();
    $path = array();    
    while($row=$query->fetch(PDO::FETCH_ASSOC)){    
        if ($row['akc_parent_sire']!='') { 
            $path[] = $row['akc_parent_sire']; 
            echo $row['akc_parent_sire'];
            $path = array_merge(getPedigree($row['akc_parent_sire']), $path); 
        } 
        if ($row['akc_parent_dam']!='') { 
            $path[] = $row['akc_parent_dam']; 
            echo $row['akc_parent_dam'];
            $path = array_merge(getPedigree($row['akc_parent_dam']), $path); 
        } 
    }       
    return $path; 
} 
print_r(getPedigree('vvv'));

json 배열을 반환하고 javascript를 사용하여 결과를 DOM 요소에 연결할 수 있도록 각 세대를 루프해야 합니다.4세대만 문의하면 되지만 CPU 사이클 비용이 걱정됩니다.일단 데이터베이스에 수십만 마리의 동물이 포함되면 이 동일한 쿼리가 얼마나 효율적일까요?

반복 호출에 의한 데이터베이스 남용을 방지하기 위해 전체 테이블을 한 번만 선택하고 결과 집합에서 모든 재귀 작업을 php에게 맡기십시오.

수정사항: 10만 행까지의 수집은 너무 힘들기 때문에 다른 조언이 있습니다.재귀 프로세스 내에서 데이터베이스에 대해 최대 31번의 개별 트립을 수행하는 것이 아니라 데이터베이스에 대한 최대 5번의 트립을 기반으로 필터링된 어레이를 구축할 것을 권장합니다.

다음 스니펫은 테스트되지 않습니다.

$generation = 1;
$needles = [1];
$animals = [];
while ($needles && $generation < 6) {
    $sth = $db->prepare("SELECT * FROM animals WHERE akc_reg_num IN (" . implode(',', array_fill(0, count($needles), '?')) . ")");
    $sth->execute($needles);
    if ($results = $sth->fetchAll(\PDO::FETCH_ASSOC)) {
        $needles = array_filter(array_merge(array_column($results, 'akc_parent_sire'), array_column($results, 'akc_parent_dam')));
        $animals[] = array_merge($animal, $results);
    } else {
        $needles = null;
    }
    ++$generation;
}
// $animals is ready to pass to the php recursion

에서$animals다음과 같은 결과 세트:

$animals = [
    ['id' => 1, 'akc_reg_num' => 1, 'akc_parent_sire' => 2, 'akc_parent_dam' => 3],
    ['id' => 2, 'akc_reg_num' => 2, 'akc_parent_sire' => 5, 'akc_parent_dam' => 6],
    ['id' => 3, 'akc_reg_num' => 3, 'akc_parent_sire' => 9, 'akc_parent_dam' => 0],
    ['id' => 4, 'akc_reg_num' => 5, 'akc_parent_sire' => 0, 'akc_parent_dam' => 0],
    ['id' => 5, 'akc_reg_num' => 6, 'akc_parent_sire' => 7, 'akc_parent_dam' => 8],
    ['id' => 6, 'akc_reg_num' => 7, 'akc_parent_sire' => 0, 'akc_parent_dam' => 0],
    ['id' => 7, 'akc_reg_num' => 8, 'akc_parent_sire' => 0, 'akc_parent_dam' => 0],
    ['id' => 8, 'akc_reg_num' => 9, 'akc_parent_sire' => 10, 'akc_parent_dam' => 11],
    ['id' => 9, 'akc_reg_num' => 10, 'akc_parent_sire' => 0, 'akc_parent_dam' => 0],
    ['id' => 10, 'akc_reg_num' => 11, 'akc_parent_sire' => 12, 'akc_parent_dam' => 0],
    ['id' => 11, 'akc_reg_num' => 12, 'akc_parent_sire' => 0, 'akc_parent_dam' => 0]
];

항목별 작업:

• 어레이를 검색하여 타깃 행을 찾습니다.akc_reg_num무한 재귀 가능성을 방지하기 위해 해당 행을 "substack"에서 삭제한 후 검색 루프를 중단하여 효율성을 극대화합니다.
• haystack에 일치하는 akc_reg_num이 없으면 빈 어레이를 반환합니다.
• 일치하는 akc_reg_num이 있는 경우 건초 스택을 루프하고 발견된 모든 부모를 다시 찾습니다.결과 어레이를 작고 깔끔하게 유지하기 위해 "deadend"를 걸러내고 있습니다.
• 부모가 모두 같은 세대에 있을 경우 불필요한 반복을 방지하기 위해 루프를 끊습니다.
• 재귀는 생성 수가 4를 초과하거나 수집할 상위 항목이 더 이상 없을 때까지 계속됩니다.

코드: (데모)

function buildPedigree($haystack, $akc_reg_num, $generation = 0) {
    ++$generation;
    foreach ($haystack as $index => $row) {
        if ($row['akc_reg_num'] == $akc_reg_num) {
            $result = ['sire' => $row['akc_parent_sire'], 'dam' => $row['akc_parent_dam']];
            unset($haystack[$index]);             // reduce the haystack to improve efficiency and avoid infinite loop
            break;                                // stop searching
        }
    }
    if (!isset($result)) {
        return [];  // $akc_reg_num not found
    }

    foreach ($haystack as $row) {
        if ($row['akc_reg_num'] == $result['sire']) {
            $result['sire_parents'] = array_filter(buildPedigree($haystack, $row['akc_reg_num'], $generation));  // recurse and purge empty parent arrays
            if (array_key_exists('dam_parents', $result)) {
                break;  // both parents found in generation, stop this loop
            }
        } elseif ($row['akc_reg_num'] == $result['dam']) {
            $result['dam_parents'] = array_filter(buildPedigree($haystack, $row['akc_reg_num'], $generation));  // recurse and purge empty parent arrays
            if (array_key_exists('sire_parents', $result)) {
                break;  // both parents found in generation, stop this loop
            }
        }
    }
    return $generation <= 4 ? $result : [];
} 

var_export(buildPedigree($animals, 1));

출력:

array (
    'sire' => 2,
    'dam' => 3,
    'sire_parents' => array (
        'sire' => 5,
        'dam' => 6,
        'dam_parents' => array (
            'sire' => 7,
            'dam' => 8,
        ),
    ),
    'dam_parents' => array (
        'sire' => 9,
        'sire_parents' => array (
            'sire' => 10,
            'dam' => 11,
            'dam_parents' => array (
                'sire' => 12,
            ),
        ),
    ),
)

언급URL : https://stackoverflow.com/questions/55327343/how-to-generate-hierarchical-result-pedigree-from-sql-query-without-cte