| Author |
 Topic  |
|
|
astralis
Starting Member
USA
35 Posts |
 Posted - 12/04/2012 : 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:
replyid
parentid
childid
replydate
But 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 12/04/2012 : 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
Starting Member
USA
35 Posts |
Posted - 12/04/2012 : 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
Flowing Fount of Yak Knowledge
3826 Posts |
|
|
robvolk
Most Valuable Yak
USA
15558 Posts |
Posted - 12/04/2012 : 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
Starting Member
USA
35 Posts |
Posted - 12/06/2012 : 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 ID
2. Reply ID
3. Parent ID
4. Child ID
5. Date
6. Reply Text |
Edited by - astralis on 12/06/2012 16:20:52 |
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
3826 Posts |
Posted - 12/06/2012 : 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
Starting Member
USA
35 Posts |
Posted - 12/08/2012 : 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
USA
15558 Posts |
Posted - 12/09/2012 : 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-sql
I 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 12/09/2012 : 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. |
Edited by - sunitabeck on 12/09/2012 08:13:59 |
|
|
|
astralis
Starting Member
USA
35 Posts |
Posted - 12/11/2012 : 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? |
Edited by - astralis on 12/11/2012 04:17:11 |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 12/11/2012 : 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
Starting Member
USA
35 Posts |
Posted - 12/11/2012 : 14:15:34
|
| Yeah, that was it. I simply needed to sort the HID. Thank you! |
|
|
| |
Topic |
|
Threaded discussions
