Link Functions

Let’s say we have 2 tables that are linked by one of three kind of links (Binary Link, ObjectPtr, Foreign Key). Often a developer needs to execute the following kinds of queries:

Show records of table A with information about the count of related records of table B. The regular query for this looks like:

(a1)

SELECT A.* (SELECT COUNT(*) FROM B WHERE B.ptr = A.ID )
FROM A

Show records of table A with an aggregative function column that is calculated by the specified column of table B for a set of related records. The regular query for this looks like:

(a2)

SELECT A.* (SELECT MIN(B.b2) FROM B WHERE B.ptr = A.ID)
FROM A

(a3)

SELECT A.* (SELECT MAX(B.b2) FROM B WHERE B.ptr = A.ID)
FROM A

(a4)

SELECT A.* (SELECT SUM(B.b2) FROM B WHERE B.ptr = A.ID)
FROM A

(a5)

SELECT A.* (SELECT AVG(B.b2) FROM B WHERE B.ptr = A.ID)
FROM A

In Valentina you can write the above queries in a more concise way, furthermore, Valentina queries will be more effective in some cases.

(b1)

SELECT A.*, COUNT_LINKED(A, link_name)
FROM A

(b2)

SELECT A.*, MIN_LINKED(A, link_name, b2)
FROM A

(б3)

SELECT A.*, MAX_LINKED(A, link_name, b2)
FROM A

(b4)

SELECT A.*, SUM_LINKED(A, link_name, b2)
FROM A

(b5)

SELECT A.*, AVG_LINKED(A, link_name, b2)
FROM A

(b6)

SELECT A.*
FROM A
WHERE link_name.count() > 0 
SELECT A.*
FROM A
WHERE link_name.count() = 0 

It is easy to see why (b1) will be faster than (a1).

• To build a cursor for the query (a1) the database should for each record of Table A do the following:

1. go to the index of the filed TableB.Ptr
2. find related records as an array.
3. count the number of records.
4. build a temporary table (probably in RAM) with one record. This table must be deleted on the following loop.

• Query (b1) uses abstraction Link. This gives the following plan of query execution:

1. go to the index and immediately return the count of linked records as a result of the function.

• For Queries a2-a5 and b2-b5 we have practically the same effectiveness. In both cases we should:

1. go to the index of field TableB.Ptr.
2. find related records.
3. apply an aggregative function to the set of found records.

So for these queries, the usage of link functions mainly simplifies the writing of a query and makes it more natural.

Another advantage can be the creation in table A of a calculated field with e.g. formula “count_linked(A, link_name)“. In this case, we can make an index on the calculated field and very effectively execute such queries as:

SELECT A.*,  
FROM A  
WHERE child_count = 3

COUNT_LINKED( TABLE_NAME, LINK_NAME [,DIRECTION] )

Returns the count of records linked to a record of the table TABLE_NAME by the link with name LINK_NAME. It never returns NULL.

Example:

Given that we have 2 tables with linked records as shown in the picture:

then the query:

SELECT FirstName, COUNT_LINKED( Person, Link_Person_phone)
FROM Person

Returns:

Robert        2
Brian         1
John          1
Peter         0

Recursive Link

If we have a table with a self-recursive link, then COUNT_LINKED() needs an additional parameter DIRECTION, which can have values ToChild or ToParent. By default, the DIRECTION parameter has the value ToChild.

Given that we have a table with links as shown in the following picture:

Given that we want to select employers and provide the count of their subordinate people. For this we should execute the following query:

SELECT Name, 
       COUNT_LINKED( Employer, Link_Subordination, ToChild) AS ‘Subordinat’
FROM Employer

The result table will be as follows:

Let we want to select employers providing the count of their managers.

SELECT Name, 
       COUNT_LINKED( Employer, Link_Subordination, ToParent) AS ‘Managers’
FROM Employer

The result table will be as follows:

MAX_LINKED( T1_NAME, T1_T2_LINK_NAME, T2_FIELD [,DIRECTION] )
MIN_LINKED ( T1_NAME, T1_T2_LINK_NAME, T2_FIELD [,DIRECTION] )
SUM_LINKED( T1_NAME, T1_T2_LINK_NAME, T2_FIELD [,DIRECTION] )
AVG_LINKED( T1_NAME, T1_T2_LINK_NAME, T2_FIELD [,DIRECTION] )

Returns the result of an aggregate function, calculated on the T2_FIELD field, for the group of records of table T2 linked to the current record of table T1 by link T1_T2_LINK_NAME.

Recursive Link

The parameter DIRECTION is used in the case of a self-recursive link. See details above, in the function COUNT_LINKED().

Example:

Given that we want to see a list of employers and determine the SUM() of the salary of their employees. For this we execute the following query:

SELECT Name,
       SUM_LINKED( Employer, Link_Subordination, Salary, ToChild) AS ‘Total’
FROM Employer

The result table: