Table of Contents
Link Functions
Standard SQL Examples
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
Link Function Examples
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
Comparison
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:
- go to the index of the filed TableB.Ptr
- find related records as an array.
- count the number of records.
- 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:
- 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:
- go to the index of field TableB.Ptr.
- find related records.
- 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
Link Function Syntax
COUNT_LINKED()
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()
MIN_LINKED()
SUM_LINKED()
AVG_LINKED()
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: