FreeSql 使用 ToTreeList/AsTreeCte 查詢無限級分類表

FreeSql發表於2020-06-28
SQLAST

關於無限級分類

第一種方案：
使用遞迴演算法，也是使用頻率最多的，大部分開源程式也是這麼處理，不過一般都只用到四級分類。 這種演算法的資料庫結構設計最為簡單。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

相關文章