Code Examples of Foreign Key Function in esProc

If field (or combination of fields) k is table M’s primary key, and k, at the same time, exists in table B, then k is regarded as the foreign key of B. The foreign key maintains a relationship between two tables and is one of the most important concepts in structured data computing. Through object references, esProc makes foreign key easy to function. The following examples aim to illustrate the operation.

Example 1 Create relationship between a referenced table and a referencing table 
order is the referencing table, emp is the referenced table, connect emp to order. Display emp'sName field, Gender field and Salary field and order's OrderID field and Amount field.
Note: Only tables emp and order are used here. Later, table dep is to be used in other examples. The relationship between the three tables over foreign key is shown as follows:

Data may originate from databases or texts. For example:
order=esProc.query("select OrderID,SellerId,Amount,OrderDate from sales")
emp=esProc.query("select EId,Name,Gender,Birthday,Dept,Salary from emp")
dep=esProc.query("select * from department")

esProc code:
A3=order.switch(SellerId, emp:EId)
A4=order.new(OrderID,Amount,SellerId.Name,SellerId.Gender,SellerId.Salary)
Computed results:
 

Code interpretation:
 A3: Replace records of SellerID in order with their counterparts in emp, create foreign key relationship between the two tables.
 A4: Get OrderID field and Amount field in order, and get Name, Gender and Salary field in emp through related references. We can see that, with object references, fields in emp can be accessed to directly from order. Thus complex and difficult join statements can be skipped.

Example 2: Query referencing table according to condition of referenced table 

Find orders signed by female sellers whose salary is greater than 10,000.
esProc code:
A3=order.switch(SellerId, emp:EId) / the same as above example
A5=order.select(SellerId.Salary>10000 && SellerId.Gender=="F")

Computed results:
 

Click the above blue hyperlinks and corresponding employee information will be shown:
 


Example 3: Group according to referenced table 
Compute sales amount of each department.
esProc code:
A3=order.switch(SellerId, emp:EId) / the same as above example
A5=order.groups(SellerId.Dept;sum(Amount))
Computed results:
 

Field names can be changed, like order.groups(SellerId.Dept:dt;sum(Amount):amt). The effect of name change is shown below:
 

Example 4: Complex association between multiple tables

For departments whose sales amount is greater than 50,000, find their managers’ names.
esProc code:
A3=order.switch(SellerId, emp:EId)
A4=dep.switch(Manager,emp:EId)
A5=emp.switch(Dept,dep:DeptNo)
A6=order.groups(SellerId.Dept:dt;sum(Amount):amt)
A7=A6.select(amt<=50000).(dt).(Manager).(Name)
Computed results:
 
Code interpretation:
A3, A4, A5：Create complete foreign key relationship.
A6: Compute sales amount of each department (See the above example). See below:
 
A7：Use object references to solve the question intuitionally. Expression A6.select(amt<=50000).(dt).(Manager).(Name) can be divided into four steps according to full stops. See below:
1. Find records whose sales amount is greater than 50,000 in A6.
2. Get records corresponding to dt field (in table dep)
3. Get records corresponding to Manager field (in table emp)
4. Get Name field.

Details are shown below:
A6.select(amt<=50000)  

.(dt)  
.(Manager)  

.(Name)