SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Threaded discussions
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

astralis
Yak Posting Veteran

USA
59 Posts

Posted - 12/04/2012 :  03:25:20  Show Profile  Reply with Quote
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

5155 Posts

Posted - 12/04/2012 :  09:24:19  Show Profile  Reply with Quote
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
Go to Top of Page

astralis
Yak Posting Veteran

USA
59 Posts

Posted - 12/04/2012 :  14:55:51  Show Profile  Reply with Quote
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?
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 12/04/2012 :  15:57:38  Show Profile  Reply with Quote
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
Go to Top of Page

robvolk
Most Valuable Yak

USA
15678 Posts

Posted - 12/04/2012 :  16:11:14  Show Profile  Visit robvolk's Homepage  Reply with Quote
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
Go to Top of Page

astralis
Yak Posting Veteran

USA
59 Posts

Posted - 12/06/2012 :  02:34:34  Show Profile  Reply with Quote
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
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 12/06/2012 :  11:04:09  Show Profile  Reply with Quote
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.
Go to Top of Page

astralis
Yak Posting Veteran

USA
59 Posts

Posted - 12/08/2012 :  22:36:49  Show Profile  Reply with Quote
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...
Go to Top of Page

robvolk
Most Valuable Yak

USA
15678 Posts

Posted - 12/09/2012 :  07:26:17  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/09/2012 :  08:12:02  Show Profile  Reply with Quote
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
Go to Top of Page

astralis
Yak Posting Veteran

USA
59 Posts

Posted - 12/11/2012 :  03:41:58  Show Profile  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/11/2012 :  12:52:58  Show Profile  Reply with Quote
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)
Go to Top of Page

astralis
Yak Posting Veteran

USA
59 Posts

Posted - 12/11/2012 :  14:15:34  Show Profile  Reply with Quote
Yeah, that was it. I simply needed to sort the HID. Thank you!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000