php - Mysql 分层查询忽略重复行(但它不应该!)

Question

我正在尝试从包含狗 ID、名称、Sire_ID、Dam_ID 的表中将谱系放在一起。所有的狗都在这张 table 上。我在 sitepoint 找到了一个看起来不错的函数，但它有一个问题，由于血统繁殖，一只狗可以拥有相同的曾祖 parent 。如果发生这种情况，此功能将无法正常工作:

$sql  = "SELECT name, sire_id, dam_id FROM dogs WHERE id IN($idsx)";

$idsx 可能看起来像这样:(25, 65, 42, 36, 99, 29, 72, 25)。由于 ID #25 在那里出现了两次，它第二次被忽略，我的谱系中断了。无论如何让它总是返回所有行？

这里是完整的函数:

    function query_ancestors($ids,$generation){
    // Make sure we have some doggies to look up
    $results = array();
    if (count($ids) < 1) return $results; // No more ancestors
    // For storing parent ids
    $parentIds = array();
    // Query all of the current generation
    $idsx = implode(',',$ids); // Makes comma delimited string
    $sql  = "SELECT name, sire_id, dam_id FROM dogs WHERE id IN($idsx)";
    $rows = mysql_query($sql);
    while ($row = mysql_fetch_assoc($rows)){
        // Add to results
        $row['generation'] = $generation;
        $results[] = $row;
        // Find the parents
        if ($row['sire_id']) $parentIds[] = $row['sire_id'];
        if ($row['dam_id'])  $parentIds[] = $row['dam_id'];
    }
    // repeat for all the parent dogs
    $generation++;
    $resultsx = query_ancestors($parentIds,$generation);
    return array_merge($results,$resultsx);
}

调用函数/输出结果:

$generation = 0;
$ids = array(8); // id of the the dog to start with
$results = query_ancestors($ids,$generation);
foreach($results as $result){
    echo "{$result['generation']} {$result['id']} {$result['name']} <br>\n";
}

更新函数:

function query_ancestors($ids,$generation){
    // Make sure we have some doggies to look up
    $results = array();
    if (count($ids) < 1) return $results; // No more ancestors
    // For storing parent ids
    $parentIds = array();
    // Query all of the current generation
    $current_gen_ids = implode(',',$ids); // Makes comma delimited string
    $sql  = "SELECT dog_name, dog_sire_id, dog_dam_id FROM dogs JOIN (UNION ALL SELECT $current_gen_ids AS dog_id) AS idsx ON idsx.dog_id = dogs.id";
    $rows = mysql_query($sql);
    while ($row = mysql_fetch_assoc($rows)){
        // Add to results
        $row['generation'] = $generation;
        $results[] = $row;
        // Find the parents
        if ($row['dog_sire_id']) $parentIds[] = $row['dog_sire_id'];
        if ($row['dog_dam_id'])  $parentIds[] = $row['dog_dam_id'];
    }
    // And here is the trick, repeat for all the parent dogs
    $generation++;
    $resultsx = query_ancestors($parentIds,$generation);
    return array_merge($results,$resultsx);
}

Answer 1

试试这个改变:

// Query all of the current generation
// Makes a table to be used for JOIN
$idsx = 'SELECT ' . implode(' AS id UNION ALL SELECT ', $ids ) . ' AS id' ;

$sql  = "SELECT dog_name, dog_sire_id, dog_dam_id FROM dogs JOIN (" 
      . $idsx . ") AS idsx ON idsx.id = dogs.id ";