{"id":71,"date":"2009-02-24T00:42:21","date_gmt":"2009-02-23T22:42:21","guid":{"rendered":"http:\/\/www.crydust.be\/blog\/?p=71"},"modified":"2009-02-24T10:18:37","modified_gmt":"2009-02-24T08:18:37","slug":"adjacency-tables","status":"publish","type":"post","link":"https:\/\/www.crydust.be\/blog\/2009\/02\/24\/adjacency-tables\/","title":{"rendered":"Adjacency tables"},"content":{"rendered":"<p>I&#8217;m well aware that adjacency tables aren&#8217;t the newest way of storing hierarchies in a database. Personally I prefer to use nested sets. But on this one project I had to work with a legacy database. It took me quite a while to find out how to retrieve an entire tree in one requests. This is obviously needed to speed up queries on large amounts of data.<\/p>\n<p>This query is here to help me remember how to do it.<\/p>\n<pre class=\"prettyprint\">\r\nDROP TABLE People;\r\n\r\nCREATE TABLE People (\r\nid INT NOT NULL PRIMARY KEY IDENTITY\r\n, boss_id INT\r\n, name VARCHAR(50)\r\n);\r\n\r\nSET IDENTITY_INSERT People ON\r\n\r\nINSERT INTO People (id, boss_id, name)\r\n          SELECT 1, NULL, 'Bert'\r\nUNION ALL SELECT 2, 1, 'Sylvie'\r\nUNION ALL SELECT 3, 1, 'Sylvia'\r\nUNION ALL SELECT 4, 2, 'Davy'\r\nUNION ALL SELECT 5, 2, 'Xavier'\r\nUNION ALL SELECT 6, 3, 'Kristof'\r\nUNION ALL SELECT 7, 3, 'Piet';\r\n\r\nWITH Bosses (id, boss_id, name, depth, hierarchy)\r\nAS (\r\n\tSELECT id\r\n\t, boss_id\r\n\t, name\r\n\t, 0\r\n\t, name\r\n\tFROM People\r\n\tWHERE boss_id IS NULL\r\n\t\r\n\tUNION ALL\r\n\t\r\n\tSELECT People.id\r\n\t, People.boss_id\r\n\t, People.name\r\n\t, Bosses.depth + 1\r\n\t, CONVERT(VARCHAR(50), Bosses.hierarchy + ' &gt; ' + People.name)\r\n\tFROM People\r\n\t, Bosses\r\n\tWHERE People.boss_id = Bosses.id\r\n)\r\nSELECT * \r\nFROM Bosses\r\nORDER BY Bosses.hierarchy;\r\n<\/pre>\n<table>\n<tr>\n<th>id<\/th>\n<th>boss_id<\/th>\n<th>name<\/th>\n<th>depth<\/th>\n<th>hierarchy<\/th>\n<\/tr>\n<tr>\n<td>1<\/td>\n<td>NULL<\/td>\n<td>Bert<\/td>\n<td>0<\/td>\n<td>Bert<\/td>\n<\/tr>\n<tr>\n<td>3<\/td>\n<td>1<\/td>\n<td>Sylvia<\/td>\n<td>1<\/td>\n<td>Bert &gt; Sylvia<\/td>\n<\/tr>\n<tr>\n<td>6<\/td>\n<td>3<\/td>\n<td>Kristof<\/td>\n<td>2<\/td>\n<td>Bert &gt; Sylvia &gt; Kristof<\/td>\n<\/tr>\n<tr>\n<td>7<\/td>\n<td>3<\/td>\n<td>Piet<\/td>\n<td>2<\/td>\n<td>Bert &gt; Sylvia &gt; Piet<\/td>\n<\/tr>\n<tr>\n<td>2<\/td>\n<td>1<\/td>\n<td>Sylvie<\/td>\n<td>1<\/td>\n<td>Bert &gt; Sylvie<\/td>\n<\/tr>\n<tr>\n<td>4<\/td>\n<td>2<\/td>\n<td>Davy<\/td>\n<td>2<\/td>\n<td>Bert &gt; Sylvie &gt; Davy<\/td>\n<\/tr>\n<tr>\n<td>5<\/td>\n<td>2<\/td>\n<td>Xavier<\/td>\n<td>2<\/td>\n<td>Bert &gt; Sylvie &gt; Xavier<\/td>\n<\/tr>\n<\/table>\n","protected":false},"excerpt":{"rendered":"<p>I&#8217;m well aware that adjacency tables aren&#8217;t the newest way of storing hierarchies in a database. Personally I prefer to use nested sets. But on this one project I had to work with a legacy database. It took me quite &hellip; <a href=\"https:\/\/www.crydust.be\/blog\/2009\/02\/24\/adjacency-tables\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[17],"tags":[],"class_list":["post-71","post","type-post","status-publish","format-standard","hentry","category-sql"],"_links":{"self":[{"href":"https:\/\/www.crydust.be\/blog\/wp-json\/wp\/v2\/posts\/71","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.crydust.be\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.crydust.be\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.crydust.be\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.crydust.be\/blog\/wp-json\/wp\/v2\/comments?post=71"}],"version-history":[{"count":3,"href":"https:\/\/www.crydust.be\/blog\/wp-json\/wp\/v2\/posts\/71\/revisions"}],"predecessor-version":[{"id":74,"href":"https:\/\/www.crydust.be\/blog\/wp-json\/wp\/v2\/posts\/71\/revisions\/74"}],"wp:attachment":[{"href":"https:\/\/www.crydust.be\/blog\/wp-json\/wp\/v2\/media?parent=71"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.crydust.be\/blog\/wp-json\/wp\/v2\/categories?post=71"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.crydust.be\/blog\/wp-json\/wp\/v2\/tags?post=71"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}