Recurse Clause for the Expand Businessobject Command

Syntax

The Recurse clause of the Expand Businessobject command expands through multiple levels of hierarchy by applying the Expand command to each business object found:


recurse [to \| all \| [preventduplicates]] \| N \|

N is any number indicating the number of levels that you want to expand. Use 0 to expand all.
all indicates that you want to expand all levels until all related business objects are found.
Include preventduplicates in the Recurse clause to avoid expanding objects more than once. If an object is connected at more than on level, it is reported at all levels where it is connected, but expanded only once. This reduces the amount of data returned and improves performance.

You should specify a recursion level with the Recurse clause. Recurse to all could be time-consuming. Recurse alone (without to) defaults to recurse to all.

This example shows use of the Expand Businessobject command with the Recurse (to all) clause:

MQL<40> expand businessobject Drawing 726592 A recurse to all;
1 Drawing from Component 726592 A
  2 As Designed from Assembly 726509 A
    3 As Designed to Component 726593 A
      4 Drawing from Drawing 726593 a
        5 Comment from Comment 123528 1
          6 Comment to Drawing 726592 A
            7 Markup from Markup 002670 1
    3 As Designed to Component 726594 A
      4 Drawing from Drawing 726594 A
        5 Markup from Markup 002590 1
    3 As Designed to Component 726595 A
      4 Drawing from Drawing 726595 A
        5 Markup from Markup 002733 1
      4 Note from Note 014258 1
        5 Note to Component 726591 B
          6 Note from Note 012499 1
          6 Photo from Photo 017012 1
          6 Photo from Photo 117012 1
          6 Drawing from Drawing 726591 C
            7 Markup from Markup 002715 1
          6 As Designed from Assembly 726596 B
            7 As Designed to Component R0056-48 A
            7 As Designed from Assembly 726590 A
              8 As Designed to Component R0045-62 B
              8 As Designed to Component 23348S C 
              8 As Designed to Component W2236-8S A
              8 Comment from Comment 013070 1
              8 Drawing from Drawing 726590 B
              8 Layout from Layout 100265 F
              8 Analysis from Stress Analysis 100345 D
                9 Specification from Specification 2278 C
                  10 Specification Change from Spec. Change Notice 00247 1
                  10 Specification Change from Spec. Change Notice 00252 1
            7 Drawing from Drawing 726596 B

This example shows use of the Expand Businessobject command with the Recurse (to all preventduplicates) clause.

MQL<15> expand businessobject Part MyPart A from recurse to all preventduplicates;
1 As Designed to Widget MyWidget A
  2 As Designed to Component MyComponent A
    3 As Designed to Component Component007 A
1 As Designed to Component Component007 A
  2 Drawing from Drawing MyDrawing A
    3 Specification from Specification MySpecification A
      4 As Designed to  Part MyPart A

In this example, object Component Component007 A is connected at multiple levels. It is reported at all levels where it appears but is expanded only once.

Where to Use Recurse to End and Recurse to Relationship

Many times it is important to get a list of all the objects connected at certain end points in a structure. For example, part of the process for analyzing an ECR is to determine which Products are affected by the Parts listed in the ECR for change. Getting a list of all these products helps determine which Change Boards need to be informed of the change.

You can use the following syntax to navigate from any point in a structure (in either direction) to a “leaf” without examining the nodes in between:


expand bus [\| from \|] relationship PATTERN [recurse to \| end [where ATTR_EXPR] \|] \| to \| \| relationship \|

PATTERN can include wildcard characters and include more than 1 relationship name or pattern, separated by a comma but no space.

These commands use a hierarchical query to perform a recursive expansion with a single SQL command, making the navigation extremely efficient, with only the end-nodes returned to the server. However, this means that the intermediate nodes in the expansion cannot be examined in any way. If a Where clause or filter is specified in the expansion, it is only applied to the end-nodes.

When show access is enabled, it is checked on the root of the expansion and only the children that are returned. In normal expands, show access is also checked on intermediate nodes, and can block access to some branches in the structure. Because of this, recurse to end/rel could potentially return more results than the leaf-nodes in a corresponding recurse to all.

All supported databases generate hierarchical/recursive SQL to implement recurse to end/recurse to relationship functionality.

Recurse to End

You can include Recurse To End with Expand Bus so that the system navigates directly to all end nodes of the structure. You could use this form of the command to identify “end items” in a BOM structure. For example, to find all references of Part MyWidget 1 following relationships of type EBOM or BOM, use:


expand bus Part MyWidget 1 to rel EBOM,BOM recurse to end;

A Recurse To End expand can optionally specify an attribute expression. Expansion along each path of the structure terminates if either the end object is reached or the attribute expression evaluates to true. The attribute end condition is not evaluated on the root object. The following statement, for example, would find either all objects in a structure that are the end object in a path or those that are older than the specified date and time:


expand bus ROOT from relationship BOM recurse to end where 'attribute[Modified Timestamp] > "2/23/2012 18:00"'

To find at most one object on which the attribute expression evaluates to true (and omit end/leaf objects that do not meet this criteria), you could use, for example:


expand bus ROOT from relationship BOM recurse to end where 'attribute[Modified Timestamp] > "2/23/2012 18:00"' select business where 'attribute[Modified Timestamp] > "2/23/2012"' limit 1;

Recurse to Relationship

Use recurse to relationship to only return those relationships (and their target objects) whose relationship type matches the last relationship in the relationship pattern. For example, to expand the structure following both EBOM and BOM relationships, but returning only those objects connected with the BOM relationship, use:


expand bus Part MyWidget 1 to rel EBOM,BOM recurse to rel;

Select operations can be applied normally on results from the new expand operations.

The Expand limit is applied only to the returned nodes of the expansion.

Use Cases

The following scenarios can be addressed using the recurse to end/rel commands:

Determine if a Part is ultimately used in a Product. A trigger or check can be run that traverses up a BOM structure looking for at least 1 product before allowing a Part to be promoted to the release state. This ensures that all Parts that are released will be picked up by the ERP planning which is always done at the Product level.
Find all Leaf Nodes (Components) in an EBOM. Get a list of actual end items in a BOM Structure. These end items represent the total number of unique parts in a system.
Find all Leaf Nodes (Components) in an EBOM that are “purchased”. The indication for “purchased” could be a different type (relationship or object type) or it might just be an attribute value on the item.
Report how many Parts in a structure have Drawings. This information can be used along with the total number of parts in a structure to calculate/report on the percent complete of the design.

Examples

Consider this structure of alternating relationships:


expand bus Part A1-v1 0 rel EBOM,BOM,”Manufactured by” from recurse to all; 1 EBOM to Part B1-v1 0 2 BOM to Part C1-v1 0 3 EBOM to Part D1-v1 0 4 BOM to Part E1-v1 0

To recurse through both EBOM,BOM relationships, but only report the end-items, use recurse to end:


expand bus Part A1-v1 0 rel EBOM,BOM from recurse to end; 1 BOM to Part E1-v1 0

To recurse through both EBOM, BOM relationships, but only report relationships of type BOM, use recurse to rel, placing the BOM relationship last in the relationship list.


expand bus Part A1-v1 0 rel EBOM,BOM from recurse to rel; 1 BOM to Part C1-v1 0 1 BOM to Part E1-v1 0

Use cases 3 and 4 relate to cases where the “terminating condition” is when you expand on a node that has a relationship of a different type coming from it. In case 3 (assuming “purchased” items are indicated by a relationship “Purchased From” that connects to the vendor), one could find them with:


expand bus Assembly MyAssembly 1 from rel "EBOM,BOM,Purchased From" recurse to rel select rel from.id;

The above would traverse all 3 relationship types in the from->to direction and return the relationships, and vendors, but the “select rel from.id” would return the object ID on the FROM end of the “Purchased From” relationships — namely the items in the BOM which are purchased.

In case 4, you could use the same approach to look for “Specification” relationships that connect BOM elements with drawings:


expand bus Assembly MyAssembly 1 from rel "EBOM,BOM,Specification" recurse to rel select rel from.id;

The objects returned by this expand would be the drawings themselves, but the “select from.id” would similarly return the object IDs of the BOM elements on the FROM ends of the Specifications relationships - namely, the items that point to the drawings.

In either of the above cases, if you really wanted the vendors or the drawings themselves, you could get the object ID on the TO end of the appropriate relationships by using “select rel to.id”.