Many-to-Many Relationship Queries

With Vault Objects, you can configure various relationships, including parent-child (hierarchical) and reference (non-hierarchical) relationships. Reference relationships can point to another object or back to the same object (self-referencing). Parent-child relationships are typically one-to-one (1:1) or one-to-many (1:M), but you can create a many-to-many (M:M) relationship by using a third (join) object. Learn more about Object Relationships in Vault Help.

This article describes how to set up many-to-many object and document relationships and then using the relationships to construct VQL queries such that a set of records from one parent can be retrieved based on another parent directly. These types of queries are possible by utilizing a third (join) object, which is a child object with two parents.

By using the inbound relationship to the join object from one of the parent objects, you can traverse (navigate) "down" from one of the parent objects to the join object. From there, you can use the outbound relationship from the join object to traverse back "up" to the second parent object. This is all done in a single query using subselect statements.

Article Contents

Part 1: Creating Many-to-Many Relationships between Objects

Part 2: Traversing Many-to-Many Relationships between Objects

Part 3: Creating Many-to-Many Relationships between Documents and Objects

Part 4: Traversing Many-to-Many Relationships between Documents and Objects

Part 1: Creating Many-to-Many Relationships between Objects

  • This section describes how to create a new join object (via the Admin UI) with fields referencing two parent objects. This creates a many-to-many relationship between the objects.
  • This section also describes how to retrieve the inbound and outbound relationship metadata (via the API) and understand their use. These relationships form the basis of the example queries in this article.
  • If you've already set up many-to-many relationships between objects and understand the concepts, you can skip this section and proceed to Part 2 below to begin building queries.
  • For information about many-to-many relationships between documents and objects, refer to Part 3 below.

Create a Many-to-Many Relationship between Two Parents and a Child (Join) Object

  1. Create a new custom object (approved_country__c). This must be done in the Admin UI. Learn how.
  2. Create a new custom field (product_field__c) on the new object. Set the "Field Type" to "Parent Object" referencing the standard object product__v.
  3. Create a new custom field (country_field__c) on the new object. Set the "Field Type" to "Parent Object" referencing the standard object country__v.

The new approved_country__c object is referred to as a "Join Object" (a child object of both the product__v and country__v objects).

Note: For the purposes of this article, we'll refer to product__v as "Parent 1" and country__v as "Parent 2". These are arbitrary names as both are simply "parent" objects of approved_country__c.

Top

Join Object Relationship Metadata: Approved Country (Child)

To retrieve the relationship metadata on the approved_country__c (Child) object, send a GET request to the /api/{VERSION}/metadata/vobjects/approved_country__c endpoint.

{
  "responseStatus": "SUCCESS",
  "object": {
    "available_lifecycles": [],
    "label_plural": "Approved Countries",
    "help_content": null,
    "prefix": "A09",
    "in_menu": true,
    "description": "Child join object with parents product and country.",
    "label": "Approved Country",
    "source": "custom",
    "modified_date": "2015-10-22T16:39:55.000Z",
    "created_by": 46916,
    "allow_attachments": false,
    "dynamic_security": false,
    "relationships": [
      {
        "field": "country_field__c",
        "relationship_name": "country_field__cr",
        "relationship_label": "Country Field",
        "relationship_type": "parent",
        "relationship_deletion": "block",
        "object": {
          "url": "/api/v13.0/metadata/vobjects/country__v",
          "label": "Country",
          "name": "country__v",
          "label_plural": "Countries",
          "prefix": "00C"
        }
      },
      {
        "field": "product_field__c",
        "relationship_name": "product_field__cr",
        "relationship_label": "Product Field",
        "relationship_type": "parent",
        "relationship_deletion": "block",
        "object": {
          "url": "/api/v13.0/metadata/vobjects/product__v",
          "label": "Product",
          "name": "product__v",
          "label_plural": "Products",
          "prefix": "00P"
        }
      }
    ],
    "urls": {
      "field": "/api/v13.0/metadata/vobjects/approved_country__c/fields/{name}",
      "record": "/api/v13.0/vobjects/approved_country__c/{id}",
      "list": "/api/v13.0/vobjects/approved_country__c",
      "metadata": "/api/v13.0/metadata/vobjects/approved_country__c"
    },      
    "fields": [
      {
        "help_content": "Field on the Approved Country object which references the Product parent object.",
        "editable": true,
        "relationship_deletion": "block",
        "label": "Product Field",
        "source": "custom",
        "type": "Object",
        "relationship_outbound_name": "product_field__cr",
        "modified_date": "2015-10-22T16:44:01.000Z",
        "created_by": 46916,
        "required": true,
        "relationship_inbound_label": "Approved Countries",
        "relationship_type": "parent",
        "name": "product_field__c",
        "list_column": true,
        "modified_by": 46916,
        "relationship_inbound_name": "approved_countries__cr",
        "created_date": "2015-10-22T16:44:01.000Z",
        "status": [
          "active__v"
        ],
        "object": {
          "url": "/api/v13.0/metadata/vobjects/product__v",
          "label": "Product",
          "name": "product__v",
          "label_plural": "Products",
          "prefix": "00P"
        },
        "order": 10
      },
      {
        "help_content": "Field on the Approved Country object which references the Country parent object.",
        "editable": true,
        "relationship_deletion": "block",
        "label": "Country Field",
        "source": "custom",
        "type": "Object",
        "relationship_outbound_name": "country_field__cr",
        "modified_date": "2015-10-22T16:44:25.000Z",
        "created_by": 46916,
        "required": true,
        "relationship_inbound_label": "Approved Countries",
        "relationship_type": "parent",
        "name": "country_field__c",
        "list_column": true,
        "modified_by": 46916,
        "relationship_inbound_name": "approved_countries__cr",
        "created_date": "2015-10-22T16:44:25.000Z",
        "status": [
          "active__v"
        ],
        "object": {
          "url": "/api/v13.0/metadata/vobjects/country__v",
          "label": "Country",
          "name": "country__v",
          "label_plural": "Countries",
          "prefix": "00C"
        },
        "order": 11
      },

Top

Parent Object Relationship Metadata: Product (Parent 1)

To retrieve the relationship metadata on the product__v (Parent 1) object, send a GET request to the /api/{VERSION}/metadata/vobjects/product__v endpoint.

    "relationships": [
      {
        "field": "product_field__c",
        "relationship_name": "approved_countries__cr",
        "relationship_label": "Approved Countries",
        "relationship_type": "child",
        "relationship_deletion": "block",
        "object": {
          "url": "/api/v13.0/metadata/vobjects/approved_country__c",
          "label": "Approved Country",
          "name": "approved_country__c",
          "label_plural": "Approved Countries",
          "prefix": "A09"
        }
      },

Top

Parent Object Relationship Metadata: Country (Parent 2)

To retrieve the relationship metadata on the country__v (Parent 2) object, send a GET request to the /api/{VERSION}/metadata/vobjects/country__v endpoint.

    "relationships": [
      {
        "field": "country_field__c",
        "relationship_name": "approved_countries__cr",
        "relationship_label": "Approved Countries",
        "relationship_type": "child",
        "relationship_deletion": "block",
        "object": {
          "url": "/api/v13.0/metadata/vobjects/approved_country__c",
          "label": "Approved Country",
          "name": "approved_country__c",
          "label_plural": "Approved Countries",
          "prefix": "A09"
        }
      },

Top

Part 2: Traversing Many-to-Many Relationships between Objects

Once you've set up relationships between the join object and two parent objects (described in Part 1 above), you can start building queries.

Query 1: Parent Object 1 to Child (Join) Object to Parent Object 2

In this query, we're starting from the product__v (Parent 1) object, traversing "down" through the approved_country__c (Child) object, and then back "up" to the country__v (Parent 2) object.

This allows us to retrieve fields and values from all three objects in a single query.

Query

SELECT id, name__v, (SELECT name__v, local_name__c, approval_status__c, country_field__cr.name__v, country_field__cr.external_id__v FROM approved_countries__cr) FROM product__v

This query can be broken down into the following steps:

  1. Retrieve the id and name__v from all products. The product__v (Parent 1) object is the "driver object" of this query.
  2. Use the approved_countries__cr relationship in a subselect to retrieve the name__v, local_name__c, and approval_status__v from the approved_country__c (Child) object.
  3. Use the approved_countries__cr relationship in a subselect to retrieve the name__v and external_id__v (using dot-notation on the country_field__cr relationship) from the country__v (Parent 2) object.

Response

{
  "responseStatus": "SUCCESS",
  "responseDetails": {
    "limit": 1000,
    "offset": 0,
    "size": 12,
    "total": 12
  },
  "data": [
    {
      "id": "00P000000000101",
      "name__v": "Nyaxa",
      "approved_countries__cr": {
        "responseDetails": {
          "limit": 250,
          "offset": 0,
          "size": 2,
          "total": 2
        },
        "data": [
          {
            "name__v": "Spain",
            "local_name__c": "Nyasená",
            "approval_status__c": "Pending",
            "country_field__cr.name__v": "Spain",
            "country_field__cr.external_id__v": "SPA-014"
          },
          {
            "name__v": "Italy",
            "local_name__c": "Nyza",
            "approval_status__c": "Approved",
            "country_field__cr.name__v": "Italy",
            "country_field__cr.external_id__v": "ITA-015"
          }
        ]
      }
    },
    {
      "id": "00P000000000102",
      "name__v": "Gludacta",
      "approved_countries__cr": {
        "responseDetails": {
          "limit": 250,
          "offset": 0,
          "size": 2,
          "total": 2
        },
        "data": [
          {
            "name__v": "United States",
            "local_name__c": "Gludacta",
            "approval_status__c": "Approved",
            "country_field__cr.name__v": "United States",
            "country_field__cr.external_id__v": "USA-012"
          },
          {
            "name__v": "Italy",
            "local_name__c": "Gludasom",
            "approval_status__c": "Approved",
            "country_field__cr.name__v": "Italy",
            "country_field__cr.external_id__v": "ITA-015"
          }
        ]
      }
    },

Top

Query 2: Parent Object 2 to Child (Join) Object to Parent Object 1

In this query, we're starting from the country__v (Parent 2) object, traversing "down" through the approved_country__c (Child) object, and then back "up" to the product__v (Parent 1) object.

Query

SELECT id, name__v, (SELECT name__v, local_name__c, approval_status__c, product_field__cr.name__v, product_field__cr.external_id__v FROM approved_countries__cr) FROM country__v

This query can be broken down into the following steps:

  1. Retrieve the id and name__v from all countries. The country__v (Parent 2) object is the "driver object" of this query.
  2. Use the approved_countries__cr relationship in a subselect to retrieve the name__v, local_name__c, and approval_status__v from the approved_country__c (Child) object.
  3. Use the approved_countries__cr relationship in a subselect to retrieve the name__v and external_id__v (using dot-notation on the product_field__cr relationship) from the product__v (Parent 1) object.

Response

{
  "responseStatus": "SUCCESS",
  "responseDetails": {
    "limit": 1000,
    "offset": 0,
    "size": 17,
    "total": 17
  },
  "data": [
    {
      "id": "1357662840400",
      "name__v": "United States",
      "approved_countries__cr": {
        "responseDetails": {
          "limit": 250,
          "offset": 0,
          "size": 1,
          "total": 1
        },
        "data": [
          {
            "name__v": "United States",
            "local_name__c": "Gludacta",
            "approval_status__c": "Approved",
            "product_field__cr.name__v": "Gludacta",
            "product_field__cr.external_id__v": "GLU-00577"
          }
        ]
      }
    },
    {
      "id": "1357662840582",
      "name__v": "Italy",
      "approved_countries__cr": {
        "responseDetails": {
          "limit": 250,
          "offset": 0,
          "size": 2,
          "total": 2
        },
        "data": [
          {
            "name__v": "Italy",
            "local_name__c": "Nyza",
            "approval_status__c": "Approved",
            "product_field__cr.name__v": "Nyaxa",
            "product_field__cr.external_id__v": "NYA-00278"
          },
          {
            "name__v": "Italy",
            "local_name__c": "Gludasom",
            "approval_status__c": "Approved",
            "product_field__cr.name__v": "Gludacta",
            "product_field__cr.external_id__v": "GLU-00577"
          }
        ]
      }
    },
    {
      "id": "1357662840631",
      "name__v": "Spain",
      "approved_countries__cr": {
        "responseDetails": {
          "limit": 250,
          "offset": 0,
          "size": 1,
          "total": 1
        },
        "data": [
          {
            "name__v": "Spain",
            "local_name__c": "Nyasená",
            "approval_status__c": "Pending",
            "product_field__cr.name__v": "Nyaxa",
            "product_field__cr.external_id__v": "NYA-00278"
          }
        ]
      }
    },

Top

Part 3: Creating Many-to-Many Relationships between Documents and Objects

  • This section describes how to create a new document object relationship field (via the Admin UI) which references the join object created in Part 1 above.
  • This creates a many-to-many relationship between documents and the object.
  • This section also describes how to retrieve the relationship metadata (via the API) and understand its use. This relationship forms the basis of the example queries in this article.
  • If you've already set up many-to-many relationships between documents and a join object and understand the concepts, you can skip this section and proceed to Part 4 below to begin building queries.

To create a many-to-many relationship between documents and a join object:

  1. Create a new document field (approved_country__c). This must be done in the Admin UI. Learn how.
  2. Set the "Field Type" to "Object" referencing the join object approved_country__c, which is a child of both the product__v and country__v fields.
  3. Leave the document field set to allow only one value.

Top

Document Object Relationship Metadata

To see the relationship metadata on the documents object, send a GET request to the /api/{VERSION}/metadata/objects/documents/properties endpoint.

{
    "responseStatus": "SUCCESS",
    "properties": [
        {
            "name": "approved_country__c",
            "scope": "DocumentVersion",
            "type": "ObjectReference",
            "required": false,
            "repeating": false,
            "systemAttribute": false,
            "editable": true,
            "setOnCreateOnly": false,
            "disabled": false,
            "objectType": "approved_country__c",
            "label": "Approved Country",
            "section": "generalProperties",
            "sectionPosition": 1000,
            "hidden": false,
            "queryable": true,
            "shared": false,
            "helpContent": "Document field which references the child join object Approved Country.",
            "definedInType": "type",
            "definedIn": "base_document__v",
            "relationshipType": "reference",
            "relationshipName": "document_approved_country__cr",
            "controllingField": "product__v"
        },

Top

Part 4: Traversing Many-to-Many Relationships between Documents and Objects

Once you've set up relationships between documents and the join object (described in Part 3 above), you can start building queries.

Query 3: Documents to Child (Join) Object to Parent Objects

In this query, we're starting from the documents object, traversing "across" through the approved_country__c (Child) object, and then back "up" to the product__v (Parent 1) and country__v (Parent 2) objects.

Query

SELECT id, name__v, type__v, (SELECT name__v, local_name__c, approval_status__c, product_field__cr.name__v, country_field__cr.name__v FROM document_approved_country__cr) FROM documents

This query can be broken down into the following steps:

  1. Retrieve the id, name__v, and type__v from all documents. The documents object is the "driver object" of this query.
  2. Use the document_approved_country__cr relationship in a subselect to retrieve the name__v, local_name__c, and approval_status__v from the approved_country__c (Child) object.
  3. Use the document_approved_country__cr relationship in a subselect to retrieve the name__v (using dot-notation on the product_field__cr relationship) from the product__v (Parent 1) object.
  4. Use the document_approved_country__cr relationship in a subselect to retrieve the name__v (using dot-notation on the country_field__cr relationship) from the country__v (Parent 2) object.

Response

{
  "responseStatus": "SUCCESS",
  "responseDetails": {
    "limit": 1000,
    "offset": 0,
    "size": 201,
    "total": 201
  },
  "data": [
    {
      "id": 381,
      "name__v": "Nyaxa and Your Health",
      "type__v": "Promotional Piece",
      "document_approved_country__cr": {
        "responseDetails": {
          "limit": 250,
          "offset": 0,
          "size": 1,
          "total": 1
        },
        "data": [
          {
            "name__v": "Italy",
            "local_name__c": "Nyza",
            "approval_status__c": "Approved",
            "product_field__cr.name__v": "Nyaxa",
            "country_field__cr.name__v": "Italy"
          }
        ]
      }
    },
    {
      "id": 382,
      "name__v": "Nyaxa Information Packet",
      "type__v": "Claim",
      "document_approved_country__cr": {
        "responseDetails": {
          "limit": 250,
          "offset": 0,
          "size": 1,
          "total": 1
        },
        "data": [
          {
            "name__v": "Spain",
            "local_name__c": "Nyasena",
            "approval_status__c": "Pending",
            "product_field__cr.name__v": "Nyaxa",
            "country_field__cr.name__v": "Spain"
          }
        ]
      }
    },
    {
      "id": 383,
      "name__v": "Nyaxa Prescribing Information",
      "type__v": "Reference Document",
      "document_approved_country__cr": {
        "responseDetails": {
          "limit": 250,
          "offset": 0,
          "size": 1,
          "total": 1
        },
        "data": [
          {
            "name__v": "Italy",
            "local_name__c": "Nyza",
            "approval_status__c": "Approved",
            "product_field__cr.name__v": "Nyaxa",
            "country_field__cr.name__v": "Italy"
          }
        ]
      }
    },
    {
      "id": 384,
      "name__v": "Gludacta Brochure",
      "type__v": "Promotional Piece",
      "document_approved_country__cr": {
        "responseDetails": {
          "limit": 250,
          "offset": 0,
          "size": 1,
          "total": 1
        },
        "data": [
          {
            "name__v": "United States",
            "local_name__c": "Gludacta",
            "approval_status__c": "Approved",
            "product_field__cr.name__v": "Gludacta",
            "country_field__cr.name__v": "United States"
          }
        ]
      }
    },
    {
      "id": 385,
      "name__v": "Gludacta Information",
      "type__v": "Reference Document",
      "document_approved_country__cr": {
        "responseDetails": {
          "limit": 250,
          "offset": 0,
          "size": 1,
          "total": 1
        },
        "data": [
          {
            "name__v": "Italy",
            "local_name__c": "Gludasom",
            "approval_status__c": "Approved",
            "product_field__cr.name__v": "Gludacta",
            "country_field__cr.name__v": "Italy"
          }
        ]
      }
    },
    {
      "id": 386,
      "name__v": "Gludacta Prescribing Information",
      "type__v": "Reference Document",
      "document_approved_country__cr": {
        "responseDetails": {
          "limit": 250,
          "offset": 0,
          "size": 1,
          "total": 1
        },
        "data": [
          {
            "name__v": "United States",
            "local_name__c": "Gludacta",
            "approval_status__c": "Approved",
            "product_field__cr.name__v": "Gludacta",
            "country_field__cr.name__v": "United States"
          }
        ]
      }
    },

Top

Query 4: Child (Join) Object to Documents and Parent Objects

In this query, we're starting from the approved_country__c (Child) object, traversing "across" to the documents object, and also "up" to the product__v (Parent 1) and country__v (Parent 2) objects.

Query

SELECT name__v, local_name__c, approval_status__c, product_field__cr.name__v, country_field__cr.name__v, (SELECT id, name__v, type__v FROM document_approved_country__cr) FROM approved_country__c

This query can be broken down into the following steps:

  1. Retrieve the name__v, local_name__c, approval_status__c from the approved_country__c object. The approved_country__c object is the "driver object" of this query.
  2. Use dot-notation on the product_field__cr relationship to retrieve the product name__v field value from the approved_country__c object. This field maps to the product__v object.
  3. Use dot-notation on the country_field__cr relationship to retrieve the country name__v field value from the approved_country__c object. This field maps to the country__v object.
  4. Use the document_approved_country__cr relationship in a subselect to retrieve the id, name__v, and type__v from the documents object.

Response

{
  "responseStatus": "SUCCESS",
  "responseDetails": {
    "limit": 1000,
    "offset": 0,
    "size": 4,
    "total": 4
  },
  "data": [
    {
      "name__v": "Italy",
      "local_name__c": "Gludasom",
      "approval_status__c": "Approved",
      "product_field__cr.name__v": "Gludacta",
      "country_field__cr.name__v": "Italy",
      "document_approved_country__cr": {
        "responseDetails": {
          "limit": 250,
          "offset": 0,
          "size": 1,
          "total": 1
        },
        "data": [
          {
            "id": 385,
            "name__v": "Gludacta Information",
            "type__v": "Reference Document"
          }
        ]
      }
    },
    {
      "name__v": "Italy",
      "local_name__c": "Nyza",
      "approval_status__c": "Approved",
      "product_field__cr.name__v": "Nyaxa",
      "country_field__cr.name__v": "Italy",
      "document_approved_country__cr": {
        "responseDetails": {
          "limit": 250,
          "offset": 0,
          "size": 2,
          "total": 2
        },
        "data": [
          {
            "id": 381,
            "name__v": "Nyaxa and Your Health",
            "type__v": "Promotional Piece"
          },
          {
            "id": 383,
            "name__v": "Nyaxa Prescribing Information",
            "type__v": "Reference Document"
          }
        ]
      }
    },
    {
      "name__v": "United States",
      "local_name__c": "Gludacta",
      "approval_status__c": "Approved",
      "product_field__cr.name__v": "Gludacta",
      "country_field__cr.name__v": "United States",
      "document_approved_country__cr": {
        "responseDetails": {
          "limit": 250,
          "offset": 0,
          "size": 2,
          "total": 2
        },
        "data": [
          {
            "id": 384,
            "name__v": "Gludacta Brochure",
            "type__v": "Promotional Piece"
          },
          {
            "id": 386,
            "name__v": "Gludacta Prescribing Information",
            "type__v": "Reference Document"
          }
        ]
      }
    },
    {
      "name__v": "Spain",
      "local_name__c": "Nyasena",
      "approval_status__c": "Pending",
      "product_field__cr.name__v": "Nyaxa",
      "country_field__cr.name__v": "Spain",
      "document_approved_country__cr": {
        "responseDetails": {
          "limit": 250,
          "offset": 0,
          "size": 1,
          "total": 1
        },
        "data": [
          {
            "id": 382,
            "name__v": "Nyaxa Information Packet",
            "type__v": "Claim"
          }
        ]
      }
    }
  ]
}