Scroll

REST - Data Joins

Joins are the relationship data objects have to each other. Join information is accessible and editable via REST.

Note. All requests require Session Id URL parameter and basic request headers. In the following examples, headers are omitted for clarity.

Join JSON

Joins are represented as JSON objects with the following properties:

Name Type Writeable Description
Id string no The unique Id of this join
EntityFrom string required-create The "from" (or "left") data object of this join
EntityTo string required-create The "to" (or "right") data object of this join
JoinType const yes ("Inner") Join Type
RelationshipType const yes ("OneToOne") Join Relation Type
Weight integer yes (0) The weight of this join
JoinColumns array of JoinColumn required The data fields which are joined

Example

{
  "Id":               "Shippers.Orders",
  "EntityFrom":       "Shippers",
  "EntityTo":         "Orders",
  "JoinType":         "Inner",
  "RelationshipType": "OneToMany",
  "Weight":           0,
  "JoinColumns": [
    {
      "ColumnFrom": "ShipperID",
      "ColumnTo":   "ShipVia"
    }
  ]
}

JoinColumn JSON

The JoinColumn objects of a join indicate which columns are used to join the data objects. Objects can be joined on multiple join columns (which are AND-ed). Each join requires one or more sets of join columns. JoinColumn objects have the following properties:

Name Type Writeable Description
ColumnFrom string required The join data field for the "from" (or "left") data object
ColumnTo string required The join data field for the "to" (or "right") data object

Example

"JoinColumns": [
  {
    "ColumnFrom": "ShipperID",
    "ColumnTo":   "ShipVia"
  }
]

List Joins

GET /rest/joins

List all the joins in the current configuration. Output is an array of objects, each representing an individual join.

Name Type Description
Id string The unique Id of this join

Available parameters

Name Type Description
entity string Show only joins that join this data object

Using curl

curl http://{webservice}/rest/joins?sid={sid} -X GET

Example response

Status: 200 OK

[
{
"Id": "Employees.Orders"
},
{
"Id": "Employees.EmployeeTerritories"
},
...
]

Show Join

GET /rest/joins/{Id}

Show the properties of the join specified by its Id.

Using curl

curl http://{webservice}/rest/joins/{Id}?sid={sid} -X GET

Example response

Status: 200 OK

{ "Id": "Orders.OrderDetails", "EntityFrom": "Orders", "EntityTo": "OrderDetails", "JoinType": "Inner", "RelationshipType": "OneToOne", "Weight": 0, "JoinColumns": [ { "ColumnFrom": "OrderID", "ColumnTo": "OrderID" } ] }

Create Join

POST /rest/joins

Using curl

curl http://{webservice}/rest/joins?sid={sid} -X POST ^
	-d @newJoin.txt

newJoin.txt

"{'EntityFrom':'Shippers','EntityTo':'Orders','RelationshipType':'OneToMany','JoinColumns':[{'ColumnFrom':'ShipperID','ColumnTo':'ShipVia'}]}"

Example response

Status: 201 Created
Location: /{webservice}/rest/Joins/Shippers.Orders

{ "Id": "Shippers.Orders", "EntityFrom": "Shippers", "EntityTo": "Orders", "JoinType": "Inner", "RelationshipType": "OneToMany", "Weight": 0, "JoinColumns": [ { "ColumnFrom": "ShipperID", "ColumnTo": "ShipVia" } ] }

Edit Join

PATCH /rest/joins/{Id}

Only supply the properties to be edited.

Using curl

curl http://{webservice}/rest/joins/{Id}?sid={sid} -X PATCH ^
	-d "{'JoinType':'LeftOuter'}"

Example response

Status: 204 No Content

Delete Join

DELETE /rest/joins/{Id}

Using curl

curl http://{webservice}/rest/joins/{Id}?sid={sid} -X DELETE

Example response

Status: 204 No Content

Hidden Article Information

Article Author
Exago Development
created 2017-02-21 17:18:46 UTC
updated 2017-04-05 21:01:13 UTC

Labels
REST, API, Web Service, Joins,
Have more questions? Submit a request