以有效和簡單的方式實現等級，父/子關系我有一張桌子create table site(site_Id int(5),parent_Id int(5),site_desc varchar2(100));領域的意義：site_Id：網站的IDparent_Id：站點的父IDsite_desc：雖然與問題無關，但它有網站的描述要求是，如果我有一個site_id作為輸入，我需要在網站下面標記所有ID。例如： A / \ B C / | \ /\ D E F G H /\ I J所有節點都是site_Id。該表包含如下數據：Site_id | Parent_ID | site_desc
_________|____________|___________
A | -1 |
B | A |
C | A |
D | B |
E | B |
F | B |
I | D |
J | D |......A是B和C的父級，依此類推。如果B是給定的輸入，那么查詢需要獲取D，E，I，F，J它目前通過循環中的多個查詢來實現，但我想在最少數量的查詢中實現這一點。我目前正在做的是::投票算法如下：Initially create a data set object which you will populate, by fetching data from the data base. Create a method which takes the parent id as parameter and returns its child nodes if present, and returns -1, if it doesnt have a child. Step1: Fetch all the rows, which doesn't have a parent(root) node.
Step2: Iterate through this result. For example if prod1 and prod2 are the initial returned nodes, in the resultset.
Iterating this RS we get prod1, and we insert a row in our DataSET obj.
Then we send the id of prod1 to getCHILD method, to get its child, and then again we iterate the returned resultset, and again call the getCHILD method, till we dont get the lowest node.我需要在數據模型約束中使用最佳優化技術。如果您有任何建議，請隨時回答。請提出建議。提前致謝。
3 回答
青春有我
TA貢獻1784條經驗 獲得超8個贊
這恰好與你的問題你所描述：出給定的鄰接表的，你想獲得一個特定父的所有子節點 -也許在一維數組，你可以很容易地迭代。
您只需對數據庫進行一次調用即可完成此操作，但有一些問題：您必須從表中返回所有行。MySQL不支持遞歸查詢，因此您必須SELECT在應用程序代碼中執行此操作。
我只是重申我上面鏈接的答案，但基本上如果你返回一個結果集（可能來自PDOStatement->fetchAll(PDO::FETCH_ASSOC)或其他方法），格式如下：
Array( [0] => Array ( [site_id] => A [parent_id] => -1 [site_desc] => testtext ) [1] => Array ( [site_id] => B [parent_id] => A [site_desc] => testtext ) [2] => Array ( [site_id] => C [parent_id] => A [site_desc] => testtext ) [3] => Array ( [site_id] => D [parent_id] => B [site_desc] => testtext ) [4] => Array ( [site_id] => E [parent_id] => B [site_desc] => testtext ) [5] => Array ( [site_id] => F [parent_id] => B [site_desc] => testtext ) [6] => Array ( [site_id] => I [parent_id] => D [site_desc] => testtext ) [7] => Array ( [site_id] => J [parent_id] => D [site_desc] => testtext ))
您可以site_id使用此遞歸函數檢索所有子/孫子/曾祖父/等等（如果您知道id）：
function fetch_recursive($src_arr, $id, $parentfound = false, $cats = array()){
foreach($src_arr as $row)
{
if((!$parentfound && $row['site_id'] == $id) || $row['parent_id'] == $id)
{
$rowdata = array();
foreach($row as $k => $v)
$rowdata[$k] = $v;
$cats[] = $rowdata;
if($row['parent_id'] == $id)
$cats = array_merge($cats, fetch_recursive($src_arr, $row['site_id'], true));
}
}
return $cats;}例如，假設您想要檢索所有子項site_id D，您將使用如下函數：
$nodelist = fetch_recursive($pdostmt->fetchAll(PDO::FETCH_ASSOC), 'D');print_r($nodelist);
輸出：
[0] => Array( [site_id] => D [parent_id] => B [site_desc] => testtext)[1] => Array( [site_id] => I [parent_id] => D [site_desc] => testtext)[2] => Array( [site_id] => J [parent_id] => D [site_desc] => testtext)
請注意，我們保留父母及其子女，孫子等的信息（無論嵌套深度如何）。
添加回答
舉報
0/150
提交
取消