EXISTS when trying to filter on a multiple relationship

BLOMASKY

I am sure you much smarter and better looking developers have already figured this out. But it took me a while to get my arms around this. (And a HUGE thanks to Mark Bailey. without his patient help, I would still be trying to figure out how to get this to work.

Overview. It is easy to use FIND and WHERE when the parameters to filter the record are either in the desired BO, or the can be followed up a chain of related records where there is a single relationship.

Example: (And look at the attached image to see an ER Diagram showing the relationships.)

If I want to find all of the PurchaseLines for vendor 1234 I can write FIND PurchaseLines WHERE PurchaseLines.ob_PurchaseOrder.ob_Vendor = 1234.

However, my requirement was to find all Inventory Items that have ever been purchased by vendor 1234. (And I needed to have the BO be the Inventory, not the PurchaseLines with a bunch of shortcuts.

With Mark's help (Ok, he really did ALL of the work, I just sat there and tried to look pretty), the following was written and tested.

FIND Items WHERE EXISTS PurchaseLine
WHERE (PurchaseLine.ob_Inventory=Inventory AND PurchaseLines.ob_PurchaseOrders.ob_Vendors=1234)

Figured it made sense to share this, since others might have been scratching their head trying to accomplish similar

Bruce

BenHayat

BLOMASKY wrote
FIND Items WHERE EXISTS PurchaseLine
WHERE (PurchaseLine.ob_Items=Items AND PurchaseLines.ob_PurchaseOrders.ob_Vendors=1234)

Bruce

Where did "Items" come to this relationship and picture?

EXISTS has saved me a lot time...

BLOMASKY

Sorry, typo... Items = Inventory

bruce

pureist

to keep it clean, perhaps edit your original post, and delete your 2nd post, Bruce.
then Ben will delete his.
and i will delete mine.

BenHayat

pureist wrote
to keep it clean, perhaps edit your original post, and delete your 2nd post, Bruce.
then Ben will delete his.
and i will delete mine.

I was about to say that! 😃

pureist

..though i do think 'item' makes more sense/is more logical than 'inventory', but that would require changing the image 😃

customaware

The diagram does not represent what you have Bruce.....

Vendor Owns Many PurchaseOrder
PurchaseOrder Owns Many PurchaseLine
PurchaseOrder Owned By Vendor
PurchaseLine Owned By PurchaseOrder
PurchaseLine Owned By Item
Item Owns Many PurchaseLine

Gotta say..... Having a BO Owned by more than one BO scares me.... Do others have experience with this?

BenHayat

eagles9999 wrote
Gotta say..... Having a BO Owned by more than one BO scares me.... Do others have experience with this?

That's a poor design From OO and DB design point of view. It can have strange results.

pureist

eagles wrote:

"Vendor Owns Many PurchaseOrder
PurchaseOrder Owned By Vendor

PurchaseOrder Owns Many PurchaseLine
PurchaseLine Owned By PurchaseOrder

Item Owns Many PurchaseLine
PurchaseLine Owned By Item"

(above has been reordered to be slightly easier to follow logic-wise)

I don't think this would be the best structure in such a vendor-order-orderline(item) application .

When something is an exclusive child (direct sibling) of something else, then an owned relationship is relevant.
But when something is not an exclusive child/direct sibling of something else, then I find it is better to have them in a Peer relationship.

Thus item and orderline would be in Peer relationship (one to many), because an orderline is not an exclusive child (direct sibling) of item, as item would be used in many different orderline's across many different purchaseorder's.

Thus, the multiple owned relationships for the same object, purchaseline, would not occur.

Jaymer

so the bottom of the diagram should be this?

PurchaseLine
ob_PurchaseOrder
ps_Item
|
|
V
Item

customaware

In my view, yes Jaymer.

Issue is that Bruce wanted a situation whereby is an Item was deleted then any PurchaseLines that were assigned that Item would also be deleted....

I disagree with that design but, hey..... Bruce has forgotten more than I will ever learn about SQL

BLOMASKY

Correct me if I am wrong, but the ONLY diff in AwareIM between Owned or Peer is that the Owned does a cascading delete. So if I do delete an owner then all children (owned by) will "poof".

Another way to look at it. If the owner is deleted, then is the data still valid. In this case, the PurchaseLine. If either the PurchaseHeader OR the Items (Inventory) record is deleted, then the PurchaseLine does not have enough information to exist on its own.

In an ideal world, life would be good if it worked in reverse. (which, SQL can do, but Aware does not), If I have an Owner / Owned by relationship then I would like to stop any deletions of the owner. (Yea, I can do that in my logic by having a process called before every deletion attempt to check to see if any children, but why not have a toggle (like "Owner") do that for me. SQL does that by either saying RESTRICT DELETE or CASCADE Delete

Bruce

BLOMASKY

BenHayat wrote
eagles9999 wrote
Gotta say..... Having a BO Owned by more than one BO scares me.... Do others have experience with this?

That's a poor design From OO and DB design point of view. It can have strange results.

What strange results Ben? I am curious.

In SQL the term is IDENTIFYING vs. NON-IDENTIFYING relationship. In other words, does this item make sense without the related data. In my example, PurchaseLines. Without a PurchaseOrder, I would have no clue who the Vendor was, or the dates, etc. Without the Inventory (Item) record, I would have no information to what item was ordered. Both of these are identifying relationships and the purchaseLine, is meaningless if either or both are missing.

So, while AwareIM does have their own way of Naming things, I am treating peer like non-identifying and owned as an identifying relationship.

Of course, most any strategy will work fine, as long as you stick with it.

Bruce

pureist

yes Bruce, one reason I proposed the Peer relationship for OrderLine (which contains an Item) and Item is because if Item instead owns OrderLine and Item is deleted, then all instances of OrderLine owned by Item would also be deleted, which could cause orphan OrderHeader records (OrderHeader's without any Orderline's) where OrderHeader's only own one OrderLine and that OrderLine contains the Item being deleted, which over time would increase in likelihood if you deleted more and more Item's.
But of course, even in a Peer relationship, if you delete an Item, then the Item attribute in OrderLine is going to be empty after Item is deleted. But for history preservation purposes you could have a calculated attribute in OrderLine which forever stores the last product code and description of the Item which was contained in that OrderLine.