树型查询✨

无限级分类（父子）是一种比较常用的表设计，每种设计方式突出优势的同时也带来缺陷，如：

  • 方法1：表设计中只有 parent_id 字段，困扰：查询麻烦（本文可解决）；
  • 方法2：表设计中冗余子级id便于查询，困扰：添加/更新/删除的时候需要重新计算；
  • 方法3：表设计中存储左右值编码，困扰：同上；

方法1设计最简单，本文解决它的递归查询问题，让使用透明化。

父子导航属性

FreeSql 导航属性之中，有针对父子关系的设置方式，如下：

  1. public class Area
  2. {
  3.   [Column(IsPrimary = true)]
  4.   public string Code { get; set; }
  6.   public string Name { get; set; }
  7.   public string ParentCode { get; set; }
  9.   [Navigate(nameof(ParentCode))]
  10.   public Area Parent { get; set; }
  11.   [Navigate(nameof(ParentCode))]
  12.   public List<Area> Childs { get; set; }
  13. }

定义 Parent 属性，在表达式中可以这样：

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

定义 Childs 属性，在表达式中可以这样（子查询）：

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

定义 Childs 属性，还可以使用【级联保存】、【贪婪加载】等等操作。

  1. fsql.Delete<Area>().Where("1=1").ExecuteAffrows();
  2. var repo = fsql.GetRepository<Area>();
  3. repo.DbContextOptions.EnableAddOrUpdateNavigateList = true;
  4. repo.DbContextOptions.NoneParameter = true;
  5. repo.Insert(new Area
  6. {
  7.   Code = "100000",
  8.   Name = "中国",
  9.   Childs = new List<Area>(new[] {
  10.     new Area
  11.     {
  12.       Code = "110000",
  13.       Name = "北京",
  14.       Childs = new List<Area>(new[] {
  15.         new Area{ Code="110100", Name = "北京市" },
  16.         new Area{ Code="110101", Name = "东城区" },
  17.       })
  18.     }
  19.   })
  20. });

1、ToTreeList

配置好父子属性之后，就可以这样用了：

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

查询数据本来是平面的，ToTreeList 方法将返回的平面数据在内存中加工为树型 List 返回。

2、AsTreeCte 递归删除

很常见的无限级分类表功能，删除树节点时，把子节点也处理一下。

  1. fsql.Select<Area>()
  2.   .Where(a => a.Name == "中国")
  3.   .AsTreeCte()
  4.   .ToDelete()
  5.   .ExecuteAffrows(); //删除 中国 下的所有记录

如果软删除：

  1. fsql.Select<Area>()
  2.   .Where(a => a.Name == "中国")
  3.   .AsTreeCte()
  4.   .ToUpdate()
  5.   .Set(a => a.IsDeleted, true)
  6.   .ExecuteAffrows(); //软删除 中国 下的所有记录

3、AsTreeCte 递归查询

若不做数据冗余的无限级分类表设计，递归查询少不了，AsTreeCte 正是解决递归查询的封装，方法参数说明：

参数 描述
(可选) pathSelector 路径内容选择，可以设置查询返回：中国 -> 北京 -> 东城区
(可选) up false(默认)：由父级向子级的递归查询，true：由子级向父级的递归查询
(可选) pathSeparator 设置 pathSelector 的连接符，默认：->
(可选) level 设置递归层级

通过测试的数据库：MySql8.0、SqlServer、PostgreSQL、Oracle、Sqlite、Firebird、达梦、人大金仓

姿势一：AsTreeCte() + ToTreeList

  1. var t2 = fsql.Select<Area>()
  2.   .Where(a => a.Name == "中国")
  3.   .AsTreeCte() //查询 中国 下的所有记录
  4.   .OrderBy(a => a.Code)
  5.   .ToTreeList(); //非必须，也可以使用 ToList（见姿势二）
  6. Assert.Single(t2);
  7. Assert.Equal("100000", t2[0].Code);
  8. Assert.Single(t2[0].Childs);
  9. Assert.Equal("110000", t2[0].Childs[0].Code);
  10. Assert.Equal(2, t2[0].Childs[0].Childs.Count);
  11. Assert.Equal("110100", t2[0].Childs[0].Childs[0].Code);
  12. Assert.Equal("110101", t2[0].Childs[0].Childs[1].Code);
  13. // WITH "as_tree_cte"
  14. // as
  15. // (
  16. // SELECT 0 as cte_level, a."Code", a."Name", a."ParentCode" 
  17. // FROM "Area" a 
  18. // WHERE (a."Name" = '中国')
  20. // union all
  22. // SELECT wct1.cte_level + 1 as cte_level, wct2."Code", wct2."Name", wct2."ParentCode" 
  23. // FROM "as_tree_cte" wct1 
  24. // INNER JOIN "Area" wct2 ON wct2."ParentCode" = wct1."Code"
  25. // )
  26. // SELECT a."Code", a."Name", a."ParentCode" 
  27. // FROM "as_tree_cte" a 
  28. // ORDER BY a."Code"

姿势二：AsTreeCte() + ToList

  1. var t3 = fsql.Select<Area>()
  2.   .Where(a => a.Name == "中国")
  3.   .AsTreeCte()
  4.   .OrderBy(a => a.Code)
  5.   .ToList();
  6. Assert.Equal(4, t3.Count);
  7. Assert.Equal("100000", t3[0].Code);
  8. Assert.Equal("110000", t3[1].Code);
  9. Assert.Equal("110100", t3[2].Code);
  10. Assert.Equal("110101", t3[3].Code);
  11. //执行的 SQL 与姿势一相同

姿势三：AsTreeCte(pathSelector) + ToList

设置 pathSelector 参数后，如何返回隐藏字段？

  1. var t4 = fsql.Select<Area>()
  2.   .Where(a => a.Name == "中国")
  3.   .AsTreeCte(a => a.Name + "[" + a.Code + "]")
  4.   .OrderBy(a => a.Code)
  5.   .ToList(a => new { 
  6.     item = a, 
  7.     level = Convert.ToInt32("a.cte_level"), 
  8.     path = "a.cte_path" 
  9.   });
  10. Assert.Equal(4, t4.Count);
  11. Assert.Equal("100000", t4[0].item.Code);
  12. Assert.Equal("110000", t4[1].item.Code);
  13. Assert.Equal("110100", t4[2].item.Code);
  14. Assert.Equal("110101", t4[3].item.Code);
  15. Assert.Equal("中国[100000]", t4[0].path);
  16. Assert.Equal("中国[100000] -> 北京[110000]", t4[1].path);
  17. Assert.Equal("中国[100000] -> 北京[110000] -> 北京市[110100]", t4[2].path);
  18. Assert.Equal("中国[100000] -> 北京[110000] -> 东城区[110101]", t4[3].path);
  19. // WITH "as_tree_cte"
  20. // as
  21. // (
  22. // SELECT 0 as cte_level, a."Name" || '[' || a."Code" || ']' as cte_path, a."Code", a."Name", a."ParentCode" 
  23. // FROM "Area" a 
  24. // WHERE (a."Name" = '中国')
  26. // union all
  28. // SELECT wct1.cte_level + 1 as cte_level, wct1.cte_path || ' -> ' || wct2."Name" || '[' || wct2."Code" || ']' as cte_path, wct2."Code", wct2."Name", wct2."ParentCode" 
  29. // FROM "as_tree_cte" wct1 
  30. // INNER JOIN "Area" wct2 ON wct2."ParentCode" = wct1."Code"
  31. // )
  32. // SELECT a."Code" as1, a."Name" as2, a."ParentCode" as5, a.cte_level as6, a.cte_path as7 
  33. // FROM "as_tree_cte" a 
  34. // ORDER BY a."Code"

更多姿势…请根据代码注释进行尝试