Operator ->

This operator is part of SQL 1999 standard. It was added into SQL to extend its Object-Relational features. This operator perfectly fits into Valentina Database Model for use with Foreign Key and ObjectPtr links because they have Pointer Nature.

This operator gives the possibility to refer any Field of the ONE Table while you work with MANY Table.

Example: Let we have tables Person and Phone linked as ONE to MANY with help of Foreign Key Field “Person_Ptr”.

Let we want to see list of phone numbers with LastName of an owner if it exists. Traditional way is to use LEFT OUTER JOIN for this:

SELECT Phone.*, Person.LastName
FROM Phone LEFT OUTER JOIN Person ON Phone.Person_ptr = Person.ID
WHERE ...

With help of opeator → this query can be as simple as:

SELECT Phone.*, Person_ptr->LastName
FROM Phone
WHERE ...

Operator consist of two parts.

• The first part is the database object which defines the link between the tables. This can be:
  1. Link name.
     
  2. The name of ObjectPtr field.
     
  3. The name of field defined as Foreign Key.
     

• The second part of the operator is the name of any field of the “opposite” Table-member of the link.

Operator → can be applied only to the links of M:1 and 1:1 types, and the referred field should belong to the Table that containes Key values (in above example “Person” Table). This guaranties, that operator always returns the single value or NULL if there is no parent record.

If more than one record in the right table corresponds to the current record we have a deal with the link of 1:М or М:М type. In this case operator → generates exception xFieldError with the error code ERR_METHOD_OUT_OF_RANGE.

As a result of operator work we get the the field value from the ONE Table inside of the current record of the MANY table.

Technically, operator →, in contrast to LEFT OUTER JOIN is realized as calculated field - “method”. I.e. its value is calculating for each record on cursor navigation.

The usage of LEFT OUTER JOIN, on the contrary - cause the building of the result during the query execution.

Operator can be used as a part any construction, which allows the usage of expression.

In the given example, as in the most of another cases, the usage of this operator is preferable to OUTER JOIN of the tables as:

1. Query structure reflects the give task in the better way.
2. Query is more readable and short.
3. The calculation of pointed field value is executed only when it is necessary (on cursor navigation).
4. There is no waste of time on building of JOIN Table. In another words the reaction of such query is higher.
5. There is no waste of disk and/or RAM space because there is no JOIN table.

At the same time there are cases when you should prefer the Table JOIN.

For example when the cursor usage expect the repeated navigation. During each record iteration, the value of the pointed field will be calculated. This may have overhead more than building of join table.

Another example - selection of several fields from the linked table. In this case we have not one but several methods of operator → type, each of them will make the search of linked records itself during cursor navigation.

Let we want to see the list of phones, which belong to a Person with name “Brian”. Query with operator → looks as:

SELECT NUMBER
FROM Phones
WHERE PersonPtr->FirstName = 'Brian'

Assume there are three tables: Customers, Orders and OrderItems

  CREATE TABLE Customers ( fName String(20) );
  CREATE TABLE Orders ( fDescription String(20), fPtrToCustomer ObjectPtr CONSTRAINT l1 REFERENCES Customers ON DELETE SET NULL );
  CREATE TABLE OrderItems( fDescription String(20), fPtrToOrder ObjectPtr CONSTRAINT l2 REFERENCES Orders ON DELETE SET NULL );

  INSERT INTO Customers VALUES ( 'Smith' );
  INSERT INTO Customers VALUES ( 'Jonson' );
 
  INSERT INTO Orders VALUES ( '1 order', 2 );
  INSERT INTO Orders VALUES ( '2 order', 2 );
  INSERT INTO Orders VALUES ( '3 order', NULL );
 
  INSERT INTO OrderItems VALUES ( '1 Item', 1 );
  INSERT INTO OrderItems VALUES ( '2 Item', 1 );
  INSERT INTO OrderItems VALUES ( '3 Item', 2 );
  INSERT INTO OrderItems VALUES ( '4 Item', 2 );
  INSERT INTO OrderItems VALUES ( '5 Item', 3 );

And you should select all orderItems for Jonson.

There are 2 common ways to accomplish the task:

1. Joins
2. Correlated sub-queries

But there is another way with using operator →.

For Joins you should write something like this:

  SELECT 
    OrderItems.fDescription 
  FROM 
    Customers INNER JOIN Orders ON l1
    INNER JOIN OrderItems ON l2
  WHERE
    Customers.fName  = 'Jonson'

In other words you have to build two joins over three tables and apply the predicate to join result. Sure - there is some Valentina internal optimization to avoid building joins on whole set of records if possible. But anyway join produces some temporary table and brings some overhead.

For sub-query way you need this one:

  SELECT 
    OrderItems.fDescription 
  FROM 
    OrderItems
  WHERE
    fPtrToOrder IN (SELECT 
      Orders.RecID
    FROM 
      Orders INNER JOIN Customers
    WHERE
      Customers.fName = 'Jonson')

or

  SELECT 
    OrderItems.fDescription 
  FROM 
    OrderItems
  WHERE
    fPtrToOrder IN (SELECT 
      Orders.RecID
    FROM 
      Orders
    WHERE
      fPtrToCustomer IN (SELECT Customers.RecID FROM Customers WHERE Customers.fName = 'Jonson'))

Actually correlated sub-query is a worst possible solution. You should not normally use it. Look - all that subqueries will be executed for each record from OrderItems table to check - does it satisfy condition or not.

And finally for “operator →” you should write as little as:

  SELECT 
    fDescription
  FROM 
    OrderItems
  WHERE
    l2->l1->fName  = 'Jonson'

or

  SELECT 
    fDescription
  FROM 
    OrderItems
  WHERE
    fPtrToOrder->fPtrToCustomer->fName  = 'Jonson'

It looks good and easy to read. You might think that predicate will be executed for each record from OrderItems but it does not. Internally, Valentina do a lot of optimizations and one of such thing is - find a set of records in OrderItems table which corresponds to Customer table record with fName = 'Jonson' in a single hop. And sure, this way is most speedy.

Now you decide to extend OrderItems with fExecuted field.

  ALTER TABLE OrderItems ADD COLUMN fExecuted BOOLEAN;

And you want to mark all Jonson's items. It is as simple as:

  UPDATE OrderItems SET fExecuted = TRUE
  WHERE
    fPtrToOrder->fPtrToCustomer->fName  = 'Jonson'