| Author |
Topic |
|
astralis
Yak Posting Veteran
62 Posts |
 Posted - 2012-12-04 : 03:25:20
|
| I did a search and found some old topics on creating threaded discussions but none of them seemed to have reach a conclusion.So, I have a table like this:replyidparentidchildidreplydateBut now that I've started to build the query, I don't know how to write it so I have the replies, parents and children (or is that childs?) in the correct order.Please advise. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-04 : 09:24:19
|
| I have not thought through the best ways to set up a threaded discussion database - but you can generate the threaded discussion from just the columns you have using a recursive query. That always may not be the simplest or most efficient approach - there may be better design patterns that would facilitate querying, inserting etc. One possible candidate that comes to mind is the HierarchyId data type available in SQL 2008 and later - take a look to see if that would suit your needs. http://technet.microsoft.com/en-us/library/bb677290(v=sql.100).aspx |
 |
|
|
astralis
Yak Posting Veteran
62 Posts |
Posted - 2012-12-04 : 14:55:51
|
| Thanks, but I'm trying to avoid a recursive query. Does anyone have any other opinions to grab and sort the order in a single query? |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-12-04 : 15:57:38
|
| Depends.. Do replies have replies? If so, how may levels? If it's N-Levels. Then you are kind of stuck writing a recursive query. If there only 1 level. Then you can just select the "parent" topics and left join that to the replies. But, it's hard to understand what you want without any sample data or expected output.Here are some links that might help you provide more relevant details:http://www.sqlservercentral.com/articles/Best+Practices/61537/http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-12-04 : 16:11:14
|
| If you're using SQL Server 2008 you can use the hierarchyid data type. You'd still use parent and child ID columns but you wouldn't need a recursive query to access them. More information here: http://msdn.microsoft.com/en-us/magazine/cc794278.aspx |
|
|
|
astralis
Yak Posting Veteran
62 Posts |
Posted - 2012-12-06 : 02:34:34
|
| Lamprey, or anyone, this is the way the data looks:TOPIC (Topic ID)COMMENTS- Reply A (Reply ID = 39, Parent ID = 0, Child ID = 0)- Reply B (Reply ID = 40, Parent ID = 0, Child ID = 0)-- First Reply to Reply B (Reply ID = 41, Parent ID = 40, Child ID = 1)---- Reply to "First Reply to Reply B" (Reply ID = 42, Parent ID = 41, Child ID = 1)-- Second Reply to Reply B (Reply ID = 43, Parent ID = 40, Child ID = 2)Database Table Structure:1. Topic ID2. Reply ID3. Parent ID4. Child ID5. Date6. Reply Text |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-12-06 : 11:04:09
|
| If you don't want to use a recurive query you can materialize the path (aka a custom Hierarchy ID) or use the built in HierarchyID as Rob suggested. |
|
|
|
astralis
Yak Posting Veteran
62 Posts |
Posted - 2012-12-08 : 22:36:49
|
| First, thanks to everyone who helped. What is the best way to use dotted numbers in a varchar field to create a sort order field based on the comments table structure I provided above?eg. 1.2.1 etc... |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-12-09 : 07:26:17
|
| There's no "best" way to set up a delimited character column to maintain a hierarchy. Using a period is probably not as good an idea as using a slash (/) since a period could be misinterpreted as a decimal number. Here's an old article that describes a method:http://www.sqlteam.com/article/more-trees-hierarchies-in-sqlI consider this to be obsolete, especially for SQL 2008, and recommend using hierarchyid instead. The notation is the same but the implementation is much better, more space efficient, and hierarchyid can be indexed to improve performance of tree operations. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-09 : 08:12:02
|
| I would second Rob's suggestion about HierarchyId. The default ToString() representation of hierarchyid is slash separated (/1/7/3 etc., with the root node being just a slash). That is easily converted to period-separated if you want to with a simple replace.HierarchyId is implemented in CLR, so the method names are case sensitive (even if your server and database are set to case in-sensitive collation). The first time I tried HierarchyId, that case sensitivity drove me up the wall for a while.Edit: Even though HierarchyId is implemented in CLR, you don't need to enable CLR on your server to be able to use it. |
|
|
|
astralis
Yak Posting Veteran
62 Posts |
Posted - 2012-12-11 : 03:41:58
|
| I'm using HierarchyID but I don't think I understand it.Below is an ascending sort on HID (using ToString) that looks like this://1/1//1/9/2//1/9/22/23/7//1/9/22/6//1/9/3//1/9/4//1/9/5//2/1//2/2//2/3//2/4/I did not expect that output. I want this output://1/1//1/9/2//1/9/3//1/9/4//1/9/5//1/9/22/6//1/9/22/23/7//2/1//2/2//2/3//2/4/What is happening and how do I achieve the behavior I want? |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-11 : 12:52:58
|
| Since I don't have the data you are using, I can't say for sure, but if you order by hid (rather than hid.ToString()) it should sort in the correct order. ToString is not a good use for sorting if you want to sort in the tree order, not only because string sort messes it up in the example you posted, but also if you insert a node between existing nodes (for example, if you insert a node between /1/1 and /1/2, it would end up being /1/1.1 or something similar) |
|
|
|
astralis
Yak Posting Veteran
62 Posts |
Posted - 2012-12-11 : 14:15:34
|
| Yeah, that was it. I simply needed to sort the HID. Thank you! |
|
|
|
|
Threaded discussions