Need Help With Many to Many DB relationship into AJAX Treeview

[johnfraser]

I have a pretty simple DB design with three items:

relationship
customer
account

a customer and account can be in multiple relationships to in the DB there is a CR table which connects the two.

Moving on... I am attempting to create the simplest possible solution to get this into an AJAX enabled tree view.

I'm following the example given here:

http://samples.infragistics.com/200...WebTree/treeloadondemand/autoloadondemand.src

but of course, the don't have a many to many relationship.

The part I'm stuck on:

                dataSet11.Relations.Add("CustOrders",
                    dataSet11.Tables["Customers"].Columns["CustomerID"],
                    dataSet11.Tables["Orders"].Columns["CustomerID"]);

                dataSet11.Relations.Add("OrderDetails",
                    dataSet11.Tables["Orders"].Columns["OrderID"],
                    dataSet11.Tables["Order Details"].Columns["OrderID"]);

            this.UltraWebTree1.DataSource = dataSet11;
            this.UltraWebTree1.Levels[0].RelationName = "CustOrders";
            this.UltraWebTree1.Levels[0].ColumnName = "CompanyName";
            this.UltraWebTree1.Levels[1].RelationName = "OrderDetails";
            this.UltraWebTree1.Levels[1].ColumnName = "OrderId";
            this.UltraWebTree1.Levels[2].ColumnName = "ProductId";
            this.UltraWebTree1.DataMember = "Customers";
            this.UltraWebTree1.DataBind();

where they setup their simple relationships... how can I do that with I have a many to many?

PS> I did try design view of the dataset and simple drag my tables into it (deleting columns I didn't need) but the "relation" elements of the dataset is always between the CR table and the parent and child.

 

[misterstick]

1. what does the supplied code have to do with accounts?

2. a treeview represents a one-to-many relationship. that's what it does and no more. how exactly are you expecting it to show a many-to-many? do you have an example of this in action?

regards,

mr s. <

 

[johnfraser]

Hi and thanks for the response. The code example is from the example I linked to. They have one to many.

Example:

Relationship 1
Relationship 2
Relationship 3

Customer 1
Customer 2

C1 belongs to R1 and R3, C2 belongs to R2 and R3

Treeview would show the following:

R1
- C1

R2
- C2

R3
- C1
- C2

Pretty straight forward "flattening" of the data. The problem is that there doesn't seem to be an easy way to bind this to the tree without manually writing a proc which does the dirty work (this is what is done now).

I can't do that now because we want to use AJAX to get just the nodes the person cares to view. The example above is a perfect example of the AJAX treeview but doesn't handle the case of many to many.

 

[johnfraser]

Answer found I'll try to explain.

My database has the following:

relationship

rel_customer_cr

customer

customer_order_cr

order

many to many relationships are done with the cr tables.

In my tree I wanted simply:

Relationship
-Customer
-Order

I ended up creating 3 adapters and the trick is to use a SQL command that "flattens" the cr and child relationship into one. A simple INNER JOIN worked:


So the example from my second post above in my DB:

relationship table
ID,NAME
1,r1
2,r2
3,r3

customer table
ID,NAME
c1,Customer1
c2, Customer2

relationship_customer_cr table
relationshipID,customerID
1,1
3,1
2,2
3,2

the adapter simply joined the customer and relationship_customer_cr table together to create a table in my dataset that was the following:

new dataset table:
relationshipID, customerID, CustomerName
1,1,Customer1
2,2,Customer2
3,1,Customer1
3,2,Customer2

The mapping worked perfectly and I have a treeview working without issues on a many to many relationship