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。下面是标上了數字的樹，同時把周遊的順序用箭頭标出來了。

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

我們稱這些數字為左值和右值（如，“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"

更多姿勢...請根據代碼注釋進行嘗試

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

關于無限級分類

引出痛點

關于 FreeSql

第一步：定義導航屬性

關于導航屬性

添加測試資料

第二步：使用 ToTreeList 傳回樹型資料

第三步：使用 AsTreeCte 遞歸查詢

繼續閱讀

高效了解 FreeSql WhereDynamicFilter，深入了解設計初衷[.NET ORM]

[開源] .Net ORM 通路 Firebird 資料庫

PostgreSQL Array 數組類型與 FreeSql 打出一套【組合拳】

【低碼】asp.net core 實體類可生産 CRUD 背景管理界面

.NET ORM 倉儲層必備的功能介紹之 FreeSql Repository 實作篇

使用Python3+Django配合vue.js的元件遞歸來實作無限級分類

Blazor入門100天 : 身份驗證和授權 (6) - 使用 FreeSql orm 管理ids資料

MySQL的分層資料管理無限級分類設計與優化

BootstrapBlazor + FreeSql ORM 實戰 Table 表格元件維護多表資料

無限極分類php實作—查子孫樹、家譜樹

遞歸周遊無限極分類菜單、菜單樹。(php版&&java版)

PHP實作遞歸無限級分類

php無限級分類

PHP實作無限級分類（遞歸+引用）原始資料遞歸引用參考文檔

mysql遞歸查詢，mysql中從子類ID查詢所有父類（做無限分類經常用到）

4. 使用RESTful、FreeSql建構簡單的部落格系統-內建AutoMapper