public object MaxInTree(IConnectionProvider conn, IEntity rootEntity, IDbColumn column, DbRelation recursiveRelation, SearchCondition leafFilter, int beginAtLevel, int endAtLevel) { EnsureRecursiveRelation(recursiveRelation); EnsureValidBeginAndEndLevels(beginAtLevel, endAtLevel); RecursiveStatement cte = new RecursiveStatement(rootEntity, recursiveRelation); cte.Select = new SelectStatement(rootEntity.Table, AggregateFunctionFactory.Max(column, null)); cte.Select.Where = CreateWhere(rootEntity, cte, leafFilter, beginAtLevel, endAtLevel); DataTable result = cte.ExecuteSelect(conn); return result.Rows[0][0]; }
public int DetermineTreeDepth(IConnectionProvider conn, IEntity rootEntity, DbRelation recursiveRelation) { EnsureRecursiveRelation(recursiveRelation); // select max(level) as maxLevelIndex from hierarchy RecursiveStatement cte = new RecursiveStatement(rootEntity, recursiveRelation); cte.Select = new SelectStatement(cte.Hierarchy, AggregateFunctionFactory.Max(cte.Hierarchy.Level, null)); DataTable results = cte.ExecuteSelect(conn); int highestLevel = (results.Rows[0][0] != DBNull.Value) ? Convert.ToInt32(results.Rows[0][0], CultureInfo.InvariantCulture) : 0; int depth = highestLevel + 1; return depth; }
public int CountTree(IConnectionProvider conn, IEntity rootEntity, DbRelation recursiveRelation, SearchCondition leafFilter, int beginAtLevel, int endAtLevel) { EnsureRecursiveRelation(recursiveRelation); EnsureValidBeginAndEndLevels(beginAtLevel, endAtLevel); RecursiveStatement cte = new RecursiveStatement(rootEntity, recursiveRelation); cte.Select = new SelectStatement(rootEntity.Table, AggregateFunctionFactory.Count(null)); cte.Select.Where = CreateWhere(rootEntity, cte, leafFilter, beginAtLevel, endAtLevel); DataTable result = cte.ExecuteSelect(conn); int count = Convert.ToInt32(result.Rows[0][0], CultureInfo.InvariantCulture); return count; }
public DataTable SelectTree(IConnectionProvider conn, IEntity rootEntity, DbRelation recursiveRelation, SearchCondition leafFilter, OrderByClause sorter, bool sortOnDb, int beginAtLevel, int endAtLevel) { EnsureRecursiveRelation(recursiveRelation); EnsureValidBeginAndEndLevels(beginAtLevel, endAtLevel); // select target.* from target where exists ... and ... RecursiveStatement cte = new RecursiveStatement(rootEntity, recursiveRelation); cte.Select = new SelectStatement(rootEntity.Table, rootEntity.Table.Columns); cte.Select.Where = CreateWhere(rootEntity, cte, leafFilter, beginAtLevel, endAtLevel); if (sortOnDb && !OrderByClause.IsNullOrEmpty(sorter)) cte.Select.OrderBy = sorter; DataTable data = cte.ExecuteSelect(conn); if (!sortOnDb && !OrderByClause.IsNullOrEmpty(sorter)) data = GenericHierarchicalQueryExecutor.SortDataTable(data, sorter); return data; }