Expanding on a_horse_with_no_name’s answer, this show how to use SQL Server’s implementation of recursive CTE (recursive single-record cross apply) in combination with row_number() to produce the exact output in the question. declare @t table(id int,parentId int,name varchar(20)) insert @t select 1, 0 ,’Category1′ insert @t select 2, 0, ‘Category2’ insert @t select 3, 1, ‘Category3’ … Read more
Use a recursive CTE: DECLARE @id INT SET @id = 3 ;WITH hierarchy AS ( SELECT t.id, t.parentid FROM YOUR_TABLE t WHERE t.id = @id UNION ALL SELECT x.id, x.parentid FROM YOUR_TABLE x JOIN hierarchy h ON h.parentid = x.id) SELECT h.id FROM hierarchy h Results: id — 3 2 1
with [CTE] as ( select * from [TheTable] c where c.[ParentId] = 1 union all select * from [CTE] p, [TheTable] c where c.[ParentId] = p.[NodeId] ) select * from [CTE]
SELECT d.`iD`, d.`subsectionOf`, CONCAT(REPEAT(‘-‘, p.`len`), d.`name`) as hier, p.`len`, p.`ancestor`, p.`descendant`, GROUP_CONCAT(crumbs.`ancestor`) AS breadcrumbs FROM `TreeData` AS d JOIN `TreePaths` AS p ON d.`iD` = p.`descendant` JOIN `TreePaths` AS crumbs ON crumbs.`descendant` = p.`descendant` WHERE p.`ancestor` = 1 GROUP BY d.`iD` ORDER BY breadcrumbs; +—-+————–+———————+—–+———-+————+————-+ | iD | subsectionOf | hier | len | ancestor … Read more
if you want to get all level child of a particular parent then you should try this select id, name, parent from (select * from tablename order by parent, id) tablename, (select @pv := ‘1’) initialisation where find_in_set(parent, @pv) > 0 and @pv := concat(@pv, ‘,’, id)
Linq-toXml handles this fine, there is an XElement.Elements()/.Nodes() operation to get immediate children, and a XElement.Descendents()/DescendentNodes() operations to get all descendents. Would you consider that as an example? To summarize Linq-to-Xml’s behavior… The navigation functions each correspond to an axis type in XPath (http://www.w3schools.com/xpath/xpath_axes.asp). If the navigation function selects Elements, the axis name is used. … Read more
Since you want the elements in the TreeView to have a list of children that consists of both Categories Products, you will want your Category ViewModel to have a collection that consists of both Categories and Products. For example, you could use a CompositeCollection to combine your existing collections: public class Category { public string … Read more
There are more options than just the two you mention. There are: Adjacency List (the “parent_id” one almost everyone uses) Nested Sets Path Enumeration Closure Table (aka Adjacency Relation) See my answer to “What is the most efficient/elegant way to parse a flat table into a tree?” Or a couple of books: “Trees and Hierarchies … Read more
Some brands of database (e.g. Oracle, Microsoft SQL Server) support extra SQL syntax to run “recursive queries” but MySQL does not support any such solution. The problem you are describing is the same as representing a tree structure in a SQL database. You just have a long, skinny tree. There are several solutions for storing … Read more
pd.Grouper allows you to specify a “groupby instruction for a target object”. In particular, you can use it to group by dates even if df.index is not a DatetimeIndex: df.groupby(pd.Grouper(freq=’2D’, level=-1)) The level=-1 tells pd.Grouper to look for the dates in the last level of the MultiIndex. Moreover, you can use this in conjunction with … Read more