StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Oracle has excellent support for hierarchical queries. You can get your document hierarchy without resorting to the <code>sortedfamily</code> column. Here's the query:</p> <pre><code>SELECT docid, PRIOR docid AS "Parent" FROM Documents START WITH parentid IS NULL CONNECT BY parentid = PRIOR docid ORDER SIBLINGS BY docid </code></pre> <p>Now to explain:</p> <pre><code>SELECT docid, PRIOR docid AS "Parent" </code></pre> <p>This gets the document and its parent on the same row by "looking back" with the <code>PRIOR</code> operator.</p> <pre><code>START WITH parentid IS NULL </code></pre> <p>This defines the hierarchy's root. Every row that has a null <code>parentid</code> is considered the root of a branch.</p> <pre><code>CONNECT BY parentid = PRIOR docid </code></pre> <p>This says that the "parent" of the current row is connected by <code>parentid</code> of the child up to <code>docid</code> of the parent.</p> <pre><code>ORDER SIBLINGS BY docid </code></pre> <p>This sorts along the entire hierarchy rather than a single value. It's hard to explain, but it works.</p> <p>The best thing about the Oracle hierarchical queries is that they'll query an entire branch, so if you have a document with a child that has a child (that has a child, and on on...) Oracle will handle it. It will also handle multiple children per parent.</p> <p>There's a <a href="http://www.sqlfiddle.com/#!4/a4e84/1" rel="nofollow">SQL Fiddle here</a> with your data plus a few additional documents.</p> <p>The Fiddle also includes a column that shows the entire "root to branch" relationship using the <code>SYS_CONNECT_BY_PATH</code> function. The <code>SYS_CONNECT_BY_PATH</code> does the same thing as your <code>sortedfamily</code> column, but it does it dynamically, without the need to maintain the column. It's also a good way to visualize each branch of the hierarchy.</p> <p><strong>Addendum</strong></p> <p>Note that the query above will return <em>every</em> branch for <em>every</em> document. If you're just interested in a single document such as <code>docid = 1000</code>, replace the <code>START WITH parentid IS NULL</code> with this:</p> <pre><code>START WITH docid = 1000 </code></pre> <p>That will give you the entire branch for <code>docid</code> 1000. If you have an index on <code>docid</code> it will be very fast.</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