One to many relationships

[neilx]

Just to clarify - EO doesn't do one to many relationships. Is that right? If that is the case, what is the best way to handle them?

I am using views already, but they - of course - give multiple rows with a 1:m relationship. That means looping through rows to get each of the many side rows.

I could also create multiple easy objects - one for each table - and simply handle them independently in my code, but that kind of defeats the object of using an object-oriented approach instead of a table driven approach.

[mgnoonan]

The only two supported ways are views and custom queries, which are essentially the same. I don't understand what you mean by \"loop through the rows\"? Can you give me an example of what you are after (and how that would be different if EO *did* support joins)?

[neilx]

Hi Matt

Long post. Sorry.

A one to many relationship example in the Northwind database is Customers:Orders. I am asking if there is any way to deal with these related tables as an object. E.g:

Code: Select all

Customer customer = new Customer();
customer.LoadByPrimaryKey(\"QUICK\")
customer.Orders.Rewind();

do
{
  Debug.WriteLine(customer.Orders.OrderDate);
} while (customer.Orders.MoveNext());

It seems like I need to do the following to get (say) the dates of the customers orders:

Code: Select all

Customers customer = new Customers();
customer.LoadByPrimaryKey(\"QUICK\");

Orders order = new Orders();
order.Where.CustomerID.Value = customer.CustomerID;
order.Query.Load();
order.Rewind();

do
{
Debug.WriteLine(order.OrderDate);
} while (order.MoveNext());

This is fairly unimportant in a simple database like Northwind, but when dealing with a real database, the coding becomes substantial. Imagine entities with lots of 1:m and several m:m relationships, then it is looking like a lot of application code is needed.

If I used a view for this example, I would maybe use the Northwind.[Orders Qry] view:

Code: Select all

OrdersQry customer = new OrdersQry();
customer.Where.CustomerID.Value = \"QUICK\";
customer.Query.Load();
do
{
  Debug.WriteLine(customer.OrderDate);
} while (customer.MoveNext());

As you see, this view will contain repeated information from the Customers table for each of the (28) orders.

Again, with just one or two of these in a database, then it is manageable, but usually one has many more such relationships to deal with. Often a table could have several such 1:m relationships, multiplying the number of rows that a view would return.

On a related subject (I raised this in another post ), a significant relationship in my databases is 1:1 or m. i.e. a table *must* have at least one row in a second table. If I could create a generated object from a view of two such interdependent tables, then EO would be very useful for me. I already have these views with triggers that manage updating both tables correctly.

At the moment, I need to temporarily rename the view, then create a table that looks like view and is named the same. The EO BusinessObject template then creates my object using this stand-in table. I can then rename the table (or delete it) and return the view back to its original name and all is then working splendidly. I can use the view as though it were a table.

The INSTEAD OF trigger on the view is called when the daab sprocs are called and thus updates the tables appropriately, ignoring the daab sprocs.

The triggers lock this solution to SQL Server, so it would be much better to have the object generation templates do this for me and use daab sprocs that understand which columns are in which tables instead of view triggers. Does that sound hard? I will have a go at customising the templates if you think it is possible and a reasonable thing to do.

[mgnoonan]

This is fairly unimportant in a simple database like Northwind, but when dealing with a real database, the coding becomes substantial. Imagine entities with lots of 1:m and several m:m relationships, then it is looking like a lot of application code is needed.

Agreed. But again, how is that different from an object platform that supports joins? You still have to write the code to loop through the rows...

If I used a view for this example, I would maybe use the Northwind.[Orders Qry] view:

Code: Select all

OrdersQry customer = new OrdersQry();
customer.Where.CustomerID.Value = "QUICK";
customer.Query.Load();
do
{
  Debug.WriteLine(customer.OrderDate);
} while (customer.MoveNext());

As you see, this view will contain repeated information from the Customers table for each of the (28) orders.

Mainly because you have chosen in your example to do a "SELECT *". You can trim the columns returned by calling .Query.AddResultColumn(). And again, this is no different from an object that supports joins, they are just doing the SELECT * for you behind the scenes.

Again, with just one or two of these in a database, then it is manageable, but usually one has many more such relationships to deal with. Often a table could have several such 1:m relationships, multiplying the number of rows that a view would return.

Absolutely. That's the challenge of a large system, and why you make the big bucks! And again, there is only one difference between the view approach and the object approach, and that's the point at which the rows are queried from the child relationship tables. With the view approach, all the rows are assembled in one query and returned at once. In the object approach (assuming a lazy-loading technique), the child rows are fetched once you reference a child object. And indeed, the child may fetch more rows than you need if you are not careful.

Let me be clear that I am not arguing against what you suggest, I'm simply maintaining that you have pretty much the same challenges regardless of how EasyObjects implements JOINs.

On a related subject (I raised this in another post ), a significant relationship in my databases is 1:1 or m. i.e. a table *must* have at least one row in a second table. If I could create a generated object from a view of two such interdependent tables, then EO would be very useful for me. I already have these views with triggers that manage updating both tables correctly.

At the moment, I need to temporarily rename the view, then create a table that looks like view and is named the same. The EO BusinessObject template then creates my object using this stand-in table. I can then rename the table (or delete it) and return the view back to its original name and all is then working splendidly. I can use the view as though it were a table.

The INSTEAD OF trigger on the view is called when the daab sprocs are called and thus updates the tables appropriately, ignoring the daab sprocs.

The triggers lock this solution to SQL Server, so it would be much better to have the object generation templates do this for me and use daab sprocs that understand which columns are in which tables instead of view triggers. Does that sound hard? I will have a go at customising the templates if you think it is possible and a reasonable thing to do.

Yes, I remember the conversation. To be honest, I just don't have the time right now. I'm glad you find EO useful, so I would encourage you to look at writing your own template and uploading it to the Template Library. That way the entire community can benefit, and you'll have improved your own ability as a template writer as well as the added benefit of not having to work around the current approach.

Keep me posted!

[mgnoonan]

Sorry, must have hit the posting limit on that last word. It should read \"approach\".

Some further thoughts:

I was a bit hasty in saying that number of rows returned is the same, it isn't. In a lazy-loaded scenario, you can do an obj.LoadByPrimaryKey() followed by a reference to a child table, which is the same as running the following queries:

Code: Select all

SELECT * FROM [parent_table] WHERE id = @id
SELECT * FROM [child_table] WHERE parent_id = @id


neilx is correct that this will return 1 row for the parent and n rows for the child, where the view will always return n rows with the parent fields repeated.

Unfortunately, until I can implement lazy-loading, the only workaround I can give you is to simulate this behavior in code:

Code: Select all

Parent_table parent = new ParentTable();
parent.Query.Load(id);

Child_table child = new Child_table();
child.Where.Parent_id.Value = id;
child.Query.Load();


[neilx]

Thanks for the thoughts. As we both have the big bucks to earn, I'll just stick with the lazy-loading workaround for now. It is, after all, what I have been doing before without an ORM anyway. EO just makes it easier. Thanks.

I'll try my hand at altering the templates when I get a chance in the next week or so.