StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>What I ended up doing is making a large join that simply ties this table to itself, over and over for every level.</p> <p>First I populate a table @topLevelGroups with just the 1st level groups (if you only have one root you can skip this step), and then @userGroups with the groups that user can see.</p> <pre><code>SELECT groupid, (level1 + CASE WHEN level2 IS NOT NULL THEN ' > ' + level2 ELSE '' END + CASE WHEN level3 IS NOT NULL THEN ' > ' + level3 ELSE '' END )as [breadcrumb] FROM ( SELECT g3.* ,g1.name as level1 ,g2.name as level2 ,g3.name as level3 FROM @topLevelGroups g1 INNER JOIN @userGroups g2 ON g2.parentid = g1.groupid and g2.groupid <> g1.groupid INNER JOIN @userGroups g3 ON g3.parentid = g2.groupid UNION SELECT g2.* ,g1.name as level1 ,g2.name as level2 ,NULL as level3 FROM @topLevelGroups g1 INNER JOIN @userGroups g2 ON g2.parentid = g1.groupid and g2.groupid <> g1.groupid UNION SELECT g1.* ,g1.name as level1 ,NULL as level2 ,NULL as level3 FROM @topLevelGroups g1 ) a ORDER BY [breadcrumb] </code></pre> <p>This is a pretty big hack, and is obviously limited to a certain number of levels (for my app, there is a reasonable limit I can pick), with the problem that the more levels are supported, it increases the number of joins exponentially, and thus is much slower. </p> <p>Doing it in code is most certainly easier, but for me that is simply not always an option - there are times when I need this available directly from a SQL query.</p> <hr> <p>I'm accepting this as the answer, since it's what I ended up doing and it may work for other people -- however, if someone can come up with a more efficient method I'll change it to them.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload