FreeSql 使用 ToTreeList/AsTreeCte 查詢無限級分類表 | 居家清潔,辦公室清潔-台北清潔公司

關於無限級分類

第一種方案：
使用遞歸算法，也是使用頻率最多的，大部分開源程序也是這麼處理，不過一般都只用到四級分類。這種算法的數據庫結構設計最為簡單。category表中一個字段id，一個字段fid(父id)。這樣可以根據WHERE id = fid來判斷上一級內容，運用遞歸至最頂層。
分析：通過這種數據庫設計出的無限級，可以說讀取的時候相當費勁，所以大部分的程序最多3-4級分類，這就足以滿足需求，從而一次性讀出所有的數據，再對得到數組或者對象進行遞歸。本身負荷還是沒太大問題。但是如果分類到更多級，那是不可取的辦法。
這樣看來這種分類有個好處，就是增刪改的時候輕鬆了…然而就二級分類而言，採用這種算法就應該算最優先了。

第二種方案：
設置fid字段類型為varchar，將父類id都集中在這個字段里，用符號隔開，比如：1,3,6
這樣可以比較容易得到各上級分類的ID，而且在查詢分類下的信息的時候，
可以使用：SELECT * FROM category WHERE pid LIKE “1,3%”。

分析：相比於遞歸算法，在讀取數據方面優勢非常大，但是若查找該分類的所有父分類或者子分類查詢的效率也不是很高，至少也要二次query，從某種意義看上，個人覺得不太符合數據庫範式的設計。倘若遞增到無限級，還需考慮字段是否達到要求，而且在修改分類和轉移分類的時候操作將非常麻煩。
暫時，在自己項目中用的就是類似第二種方案的解決辦法。就該方案在我的項目中存在這樣的問題，如果當所有數據記錄達到上萬甚至10W以上后，一次性將所以分類，有序分級的現實出來，效率很低。極有可能是項目處理數據代碼效率低帶來的。現在正在改良。

第三種方案：
　　無限級分類—-改進前序遍歷樹
那么理想中的樹型結構應具備哪些特點呢？數據存儲冗餘小、直觀性強；方便返回整個樹型結構數據；可以很輕鬆的返回某一子樹（方便分層加載）；快整獲以某節點的祖譜路徑；插入、刪除、移動節點效率高等等。帶着這些需求我查找了很多資料，發現了一種理想的樹型結構數據存儲及操作算法，改進的前序遍歷樹模型（The Nested Set Model）。
原理：
我們先把樹按照水平方式擺開。從根節點開始（“Food”），然後他的左邊寫上1。然後按照樹的順序（從上到下）給“Fruit”的左邊寫上2。這樣，你沿着樹的邊界走啊走（這就是“遍歷”），然後同時在每個節點的左邊和右邊寫上数字。最後，我們回到了根節點“Food”在右邊寫上18。下面是標上了数字的樹，同時把遍歷的順序用箭頭標出來了。

我們稱這些数字為左值和右值（如，“Food”的左值是1，右值是18）。正如你所見，這些数字按時了每個節點之間的關係。因為“Red”有3和6兩個值，所以，它是有擁有1-18值的“Food”節點的後續。同樣的，我們可以推斷所有左值大於2並且右值小於11的節點，都是有2-11的“Fruit” 節點的後續。這樣，樹的結構就通過左值和右值儲存下來了。這種數遍整棵樹算節點的方法叫做“改進前序遍歷樹”算法。

表結構設計：

那么我們怎樣才能通過一個SQL語句把所有的分類都查詢出來呢，而且要求如果是子類的話前面要打幾個空格以表現是子分類。要想查詢出所有分類很好辦：SELECT * FROM category WHERE lft>1 AND lft<18 ORDER BY lft這樣的話所有的分類都出來了，但是誰是誰的子類卻分不清，那麼怎麼辦呢?我們仔細看圖不難發現如果相鄰的兩條記錄的右值第一條的右值比第二條的大那么就是他的父類，比如food的右值是18而fruit的右值是11 那麼food是fruit的父類，但是又要考慮到多級目錄。於是有了這樣的設計，我們用一個數組來存儲上一條記錄的右值，再把它和本條記錄的右值比較，如果前者比後者小，說明不是父子關係，就用array_pop彈出數組，否則就保留，之後根據數組的大小來打印空格。

以上內容引用出處：https://www.cnblogs.com/badboys/p/9945296.html

關於第三種設計的更多資料請點擊查看原文，因為過於複雜（過重）被使用的頻率不高。

引出痛點

無限級分類（父子）是一種比較常用的表設計，每種設計方式突出優勢的同時也帶來缺陷，如：

第一種方案：表設計中只有 parent_id 字段，寫入數據方便，困擾：查詢麻煩，許多使用了 ORM 的項目被迫使用 SQL 解決該場景；
第二種方案：表設計中冗餘子級id便於查詢，困擾：添加/更新/刪除的時候需要重新計算；
第三種方案：表設計中存儲左右值編碼，困擾：同上；

第一種方案的設計最簡單，本文後面的內容是在該基礎上，使用 FreeSql 實現 ToTreeList(內存加工樹型)、AsTreeCte(實現遞歸向下/向上查詢)，滿足大眾日常使用。

關於 FreeSql

FreeSql 是功能強大的對象關係映射技術(O/RM)，支持 .NETCore 2.1+ 或 .NETFramework 4.0+ 或 Xamarin，以 MIT 開源協議託管於 github，單元測試數量 4528個，nuget 下載量 151K，支持 MySql/SqlServer/PostgreSQL/Oracle/Sqlite/達夢/人大金倉/神州通用/Access；

源碼地址：https://github.com/dotnetcore/FreeSql

作者說過：每一個功能代表他的一撮頭髮！

第一步：定義導航屬性

FreeSql 導航屬性之中，有針對父子關係的設置方式，ToTreeList/AsTreeCte 依賴該設置，如下：

public class Area
{
  [Column(IsPrimary = true)]
  public string Code { get; set; }

  public string Name { get; set; }
  public string ParentCode { get; set; }

  [Navigate(nameof(ParentCode)), JsonIgnore] //JsonIgnore 是 json.net 的特性
  public Area Parent { get; set; }
  [Navigate(nameof(ParentCode))]
  public List<Area> Childs { get; set; }
}

關於導航屬性

定義 Parent 屬性，在表達式中可以這樣：

fsql.Select<Area>()
  .Where(a => a.Parent.Parent.Parent.Name == "中國")
  .First();

定義 Childs 屬性，在表達式中可以這樣（子查詢）：

fsql.Select<Area>()
  .Where(a => a.Childs.AsSelect().Any(c => c.Name == "北京"))
  .First();

定義 Childs 屬性，還可以使用【級聯保存】、【貪婪加載】等等操作。

添加測試數據

fsql.Delete<Area>().Where("1=1").ExecuteAffrows();
var repo = fsql.GetRepository<Area>();
repo.DbContextOptions.EnableAddOrUpdateNavigateList = true;
repo.DbContextOptions.NoneParameter = true;
repo.Insert(new Area
{
  Code = "100000",
  Name = "中國",
  Childs = new List<Area>(new[] {
    new Area
    {
      Code = "110000",
      Name = "北京",
      Childs = new List<Area>(new[] {
        new Area{ Code="110100", Name = "北京市" },
        new Area{ Code="110101", Name = "東城區" },
      })
    }
  })
});

第二步：使用 ToTreeList 返回樹型數據

配置好父子屬性之後，就可以這樣用了：

var t1 = fsql.Select<Area>().ToTreeList();
Assert.Single(t1);
Assert.Equal("100000", t1[0].Code);
Assert.Single(t1[0].Childs);
Assert.Equal("110000", t1[0].Childs[0].Code);
Assert.Equal(2, t1[0].Childs[0].Childs.Count);
Assert.Equal("110100", t1[0].Childs[0].Childs[0].Code);
Assert.Equal("110101", t1[0].Childs[0].Childs[1].Code);

查詢數據本來是平面的，ToTreeList 方法將返回的平面數據在內存中加工為樹型 List 返回。

[
  {
    "ParentCode": null,
    "Childs": [
      {
        "ParentCode": "100000",
        "Childs": [
          {
            "ParentCode": "110000",
            "Childs": [],
            "Code": "110100",
            "Name": "北京市"
          },
          {
            "ParentCode": "110000",
            "Childs": [],
            "Code": "110101",
            "Name": "東城區"
          }
        ],
        "Code": "110000",
        "Name": "北京"
      }
    ],
    "Code": "100000",
    "Name": "中國"
  }
]

第三步：使用 AsTreeCte 遞歸查詢

若不做數據冗餘的無限級分類表設計，遞歸查詢少不了，AsTreeCte 正是解決遞歸查詢的封裝，方法參數說明：

參數	描述
(可選) pathSelector	路徑內容選擇，可以設置查詢返回：中國 -> 北京 -> 東城區
(可選) up	false(默認)：由父級向子級的遞歸查詢，true：由子級向父級的遞歸查詢
(可選) pathSeparator	設置 pathSelector 的連接符，默認：->
(可選) level	設置遞歸層級

通過測試的數據庫：MySql8.0、SqlServer、PostgreSQL、Oracle、Sqlite、達夢、人大金倉

姿勢一：AsTreeCte() + ToTreeList

var t2 = fsql.Select<Area>()
  .Where(a => a.Name == "中國")
  .AsTreeCte() //查詢 中國 下的所有記錄
  .OrderBy(a => a.Code)
  .ToTreeList(); //非必須，也可以使用 ToList（見姿勢二）
Assert.Single(t2);
Assert.Equal("100000", t2[0].Code);
Assert.Single(t2[0].Childs);
Assert.Equal("110000", t2[0].Childs[0].Code);
Assert.Equal(2, t2[0].Childs[0].Childs.Count);
Assert.Equal("110100", t2[0].Childs[0].Childs[0].Code);
Assert.Equal("110101", t2[0].Childs[0].Childs[1].Code);
// WITH "as_tree_cte"
// as
// (
// SELECT 0 as cte_level, a."Code", a."Name", a."ParentCode" 
// FROM "Area" a 
// WHERE (a."Name" = '中國')

// union all

// SELECT wct1.cte_level + 1 as cte_level, wct2."Code", wct2."Name", wct2."ParentCode" 
// FROM "as_tree_cte" wct1 
// INNER JOIN "Area" wct2 ON wct2."ParentCode" = wct1."Code"
// )
// SELECT a."Code", a."Name", a."ParentCode" 
// FROM "as_tree_cte" a 
// ORDER BY a."Code"

姿勢二：AsTreeCte() + ToList

var t3 = fsql.Select<Area>()
  .Where(a => a.Name == "中國")
  .AsTreeCte()
  .OrderBy(a => a.Code)
  .ToList();
Assert.Equal(4, t3.Count);
Assert.Equal("100000", t3[0].Code);
Assert.Equal("110000", t3[1].Code);
Assert.Equal("110100", t3[2].Code);
Assert.Equal("110101", t3[3].Code);
//執行的 SQL 與姿勢一相同

姿勢三：AsTreeCte(pathSelector) + ToList

設置 pathSelector 參數后，如何返回隱藏字段？

var t4 = fsql.Select<Area>()
  .Where(a => a.Name == "中國")
  .AsTreeCte(a => a.Name + "[" + a.Code + "]")
  .OrderBy(a => a.Code)
  .ToList(a => new { 
    item = a, 
    level = Convert.ToInt32("a.cte_level"), 
    path = "a.cte_path" 
  });
Assert.Equal(4, t4.Count);
Assert.Equal("100000", t4[0].item.Code);
Assert.Equal("110000", t4[1].item.Code);
Assert.Equal("110100", t4[2].item.Code);
Assert.Equal("110101", t4[3].item.Code);
Assert.Equal("中國[100000]", t4[0].path);
Assert.Equal("中國[100000] -> 北京[110000]", t4[1].path);
Assert.Equal("中國[100000] -> 北京[110000] -> 北京市[110100]", t4[2].path);
Assert.Equal("中國[100000] -> 北京[110000] -> 東城區[110101]", t4[3].path);
// WITH "as_tree_cte"
// as
// (
// SELECT 0 as cte_level, a."Name" || '[' || a."Code" || ']' as cte_path, a."Code", a."Name", a."ParentCode" 
// FROM "Area" a 
// WHERE (a."Name" = '中國')

// union all

// SELECT wct1.cte_level + 1 as cte_level, wct1.cte_path || ' -> ' || wct2."Name" || '[' || wct2."Code" || ']' as cte_path, wct2."Code", wct2."Name", wct2."ParentCode" 
// FROM "as_tree_cte" wct1 
// INNER JOIN "Area" wct2 ON wct2."ParentCode" = wct1."Code"
// )
// SELECT a."Code" as1, a."Name" as2, a."ParentCode" as5, a.cte_level as6, a.cte_path as7 
// FROM "as_tree_cte" a 
// ORDER BY a."Code"

更多姿勢…請根據代碼註釋進行嘗試

源碼地址：https://github.com/dotnetcore/FreeSql

本站聲明:網站內容來源於博客園,如有侵權,請聯繫我們,我們將及時處理

【其他文章推薦】

※為什麼 USB CONNECTOR 是電子產業重要的元件?

※網頁設計一頭霧水該從何著手呢? 台北網頁設計公司幫您輕鬆架站!

※台北網頁設計公司全省服務真心推薦

※想知道最厲害的網頁設計公司"嚨底家"!

※新北清潔公司,居家、辦公、裝潢細清專業服務

※推薦評價好的iphone維修中心