Best way or returning hierarchical data

[Daisy]

Let's say I've got a forum, where users can be moderators of each forum.

Tables look like this:

USER
--------
user_key
name

FORUM
---------
forum_key
name

USERFORUM
----------
userforum_key
user_fkey
forum_fkey

(I'm sure you can work out where the relationships are!)

My user, "Danny" is a moderator in "Forum1" and "Forum2".

I've just logged in as Danny, and my User.Authenticate() method should go to
the database, check the details, and return all the info to populat the User
object with as little effort as possible (ie. 1 query if possible).

Most of the user object is just string variables, but there's also gotta be
a list of forums I can moderate. I'm open to suggestions of how this should
actually be stored (like an int[] of the keys, should be fine).

My question, is this... How is the "best" way to pull back these forum_keys
that I'm allowed to moderate? If I join to the table, I also pull back my
user details once for every forum (twice, in this case). This seems a bit
messy.

Stored procedures don't seem to be capable of returning more than a flat
table. How would you achieve what I'm trying?

Many thanks,
Danny

 

[Guest]

You should store it in a table like this:

Moderates
------------
user_key
forum_key

To pull back the list of forums you can moderate you would do the following

SELECT forum_fkey FROM moderates WHERE user_fkey = xxxxxx

or if you have the user name but not their key you can do this:

SELECT forum_fkey FROM user a,moderates b WHERE name='danny' and a.user_key = b.user_key

 

[Daisy]

Moderates
------------
user_key
forum_key

To pull back the list of forums you can moderate you would do the following

SELECT forum_fkey FROM moderates WHERE user_fkey = xxxxxx

or if you have the user name but not their key you can do this:

SELECT forum_fkey FROM user a,moderates b WHERE name='danny' and

a.user_key = b.user_key

Sorry, I mustn't have explain correctly. I can do that bit fine, it's trying
to return "a row" or user data and "x rows" or forum keys from a single
stored procedure call I'm trying to to find the best way for

 

[BMermuys]

Hi,

Let's say I've got a forum, where users can be moderators of each forum.

Tables look like this:

USER
--------
user_key
name

FORUM
---------
forum_key
name

USERFORUM

First of all, I'm no db expert. And I don't think two queries is a shame.

However ADO does support hierarchical recordset, where the values of one
column are recordsets representing the child rows.

ADO.NET supports this too, for DataReader this almost the same. The values
of one column can be casted to another DataReader (containing the child
data).

And for an DataAdapter, the fill method can create two tables at once.

Now, to query for a hierarchical recordset you can use the Shape provider.
The shape provider provides the hierarchy while it uses other providers to
get the data.

See this sample suited for your case:

OleDbConnection conn = new OleDbConnection(
"Provider=MSDataShape;Data Provider=Microsoft.JET.OLEDB.4.0;" +
"Data Source=yourdb.mdb" );

conn.Open();
OleDbDataAdapter adap = new OleDbDataAdapter(
"SHAPE {SELECT user_key, name FROM tblUser WHERE name=?} " +
" APPEND ({SELECT forum_key, user_fkey, name FROM tblForum,tblUserForum
" +
"WHERE forum_key = forum_fkey } AS forum " +
" RELATE user_key TO user_fkey)", conn);

adap.SelectCommand.Parameters.Add("?", "the name of the user you want info
about");

DataSet ds = new DataSet();

adap.Fill (ds, "users");

foreach (DataRow dr in ds.Tables["users"].Rows)
{
Console.WriteLine("{0},{1}", dr["user_key"], dr["name"] );
foreach (DataRow dr2 in dr.GetChildRows(ds.Relations[0]))
{
Console.WriteLine("\t{0},{1}", dr2["forum_key"], dr2["name"] );
}
}
conn.Close();


This sample will generate hierarchical dataset, containing two tables (users
& forum) and a relation between them.

It will then show all rows from the parent table(users) and then for each
row it will show all child rows(forums).

In your case there will be only one row (one user) so it's an overhead here
to enumerate all rows in the users table. (Just use the first row, and if
there is none then the user account doesn't exist)

Just to note that it's possible to remove the where clause in the first
query. So that the users table would contain all rows (all users) and each
of those rows would have childrows representing the forums they can operate.

HTH
greetings

(I'm sure you can work out where the relationships are!)

My user, "Danny" is a moderator in "Forum1" and "Forum2".

I've just logged in as Danny, and my User.Authenticate() method should go to
the database, check the details, and return all the info to populat the User
object with as little effort as possible (ie. 1 query if possible).

Most of the user object is just string variables, but there's also gotta be
a list of forums I can moderate. I'm open to suggestions of how this should
actually be stored (like an int[] of the keys, should be fine).

My question, is this... How is the "best" way to pull back these forum_keys
that I'm allowed to moderate? If I join to the table, I also pull back my
user details once for every forum (twice, in this case). This seems a bit
messy.

Stored procedures don't seem to be capable of returning more than a flat
table. How would you achieve what I'm trying?

Many thanks,
Danny

 

[Daisy]

Just to note that it's possible to remove the where clause in the first
query. So that the users table would contain all rows (all users) and each
of those rows would have childrows representing the forums they can

operate.

Yep, I get that. I started using Datashaping with classic ASP a few months
back, however this involved the query being in ADO, and not in a stored
procedure, and we're trying to keep as much as possible in SP's...

Just thinking about it, if I have two SELECT queries in a stored proc, and
use ExecuteDataset on MS's SqlHelper, it might return a DataSet with two
DataTables - also quicker than two seperate calls to ExecuteDataset...?