Link Clauses (OLAP)

[Table of Contents](https://github.com/dell-oss/Doradus/wiki/OLAP Databases: Table-of-Contents) | Previous | Next
Doradus Query Language: Link Clauses

---

Link fields can be compared for a single value using an object ID as the value. Example:

Manager=def413

A link can also be tested for membership in a set of values using the IN operator:

DirectReports IN (zyxw098, ghj780)
DirectReports = (zyxw098, ghj780)  

The two examples above are equivalent. Inequalities and range functions are not allowed for link fields.

Links can also be used in path expressions, which are described in the following sections.

Link Path Clauses

A clause can search a field of an object that is related to the perspective object by using a link path. The general form of a link path is:

field1.field2...fieldN

The field names used in a link path must follow these rules:

• The first field (field1) must be a link field defined in the query's perspective table.
• All fields in the path must be link fields except for the last field, which can be a link or scalar field.
• Each secondary field (field2 through fieldN) must be a field that belongs to the extent table of the prior (immediate left) link field. That is, field2 must be a field owned by the extent table of field1, field3 must be owned by extent table of field2, and so forth.
• If the second-to-last field (fieldN-1) is a timestamp field, the last field can be a timestamp subfield (YEAR, HOUR, etc.)

The right-most field in the link path is the comparison field. The type of the target value(s) in the clause must match the type of the comparison field. For example, if the comparison field is an integer, the target values must be integers; if the comparison field is a link, the target values must be object IDs; etc. Implicit quantification occurs for every field in the path. Consider these examples:

Manager.Name : Fred
Sender.MessageAddress.Domain.Name = 'hotels.com'
DirectReports.DirectReports.FirstName = [Fred TO Wilma}  

In order, these examples are interpreted as follows:

• A perspective object (a Person) is selected if at least one of its manager's name contains the term Fred.
• A perspective object (a Message) is selected if it has at lease one sender with at least one address with at lease one domain named hotels.com.
• A perspective object (a Person) is selected if at least one direct report has at least one second-level direct report whose FirstName is >= Fred but < Wilma.

WHERE Filter

Sometimes we need multiple selection clauses for the objects in a link path, but we need the clauses to be "bound" to the same instances. To illustrate this concept, consider this query: Suppose we want to find messages where an internal recipient is within the R&D department and in an office in Kanata. We might try to write the query like this:

// Doesn't do what we want
GET /Email/Message/_query?q=InternalRecipients.Person.Department='R&D' AND  
InternalRecipients.Person.Office='Kanata'&range=0  

But the problem is that the two InternalRecipients.Person paths are separately quantified with ANY, so the query will return messages that have at least one internal recipient in R&D (but not necessarily in Kanata) while another internal recipient is in Kanata (but not necessarily in R&D). It might be tempting to quantify the two InternalRecipient.Person paths with ALL:

// Still not what we want
GET /Email/Message/_query?q=ALL(InternalRecipients.Person).Department='R&D' AND  
ALL(InternalRecipients.Person).Office='Kanata'&range=0  

Now the problem is that the query won't select messages that have one or more internal recipients who are not in R&D/Kanata, even though there might be another recipient who is.

What we really need is for the two InternalRecipient.Person clauses to be bound, meaning they apply to the same instances and are not separately quantified.

The WHERE filter can be used for this scenario, as shown below:

GET /Email/Message/_query?q=InternalRecipients.Person.WHERE(Department='R&D' AND Office='Kanata')  
&range=0  

The WHERE function is appended to the portion of the link path for which we need multiple selection clauses, and the clauses are specified as a parameter. The field names referenced in the WHERE expression are qualified to the object to the left of the WHERE clause. In the example above, Department and Office are qualified to Person, so they must be fields belonging to those objects. Note that implicit quantification takes places in the example above, hence it is identical to the following query:

GET /Email/Message/_query?q=ANY(InternalRecipients.Person).WHERE(Department='R&D' AND Office='Kanata')&range=0  

Outer WHERE Filter

The WHERE filter usually follows a link name to select related objects connected via that link. However, a link path can begin when a WHERE filter, in which case it selects perspective objects. For example:

GET /Email/Person/_query?q=WHERE(Department:sales)&range=0

This WHERE filter selects perspective objects, in this case Person. The query above is identical to the following:

GET /Email/Person/_query?q=Department:sales&range=0

The scope of an outer WHERE filter remains at the perspective object. Hence, multiple, outer WHERE filters can be chained together as in the following example:

GET /Email/Person/_query?q=WHERE(Department:sales).WHERE(Office:aliso)&range=0

The outer WHERE filters are AND-ed together, so the example above is identical to this query:

GET /Email/Person/_query?q=Department:salesANDOffice:aliso&range=0

Outer WHERE filters allow aliases to be defined as link paths that can be used in multiple contexts. For example, assume the following alias is defined:

"Person": {
	aliases: {
		"$SalesPeople": {"expression": "WHERE(Department:sales)"}
	}
	...
}  

The alias $SalesPeople can be used as a selection expression or link filter whenever the expression scope is Person. For example, the alias can be used in the following queries:

GET /Email/Person/_query?q=$SalesPeople&range=0
GET /Email/Person/_query?q=$SalesPeople.WHERE(Office:aliso)&range=0
GET /Email/Message/_query?q=Sender.Person.$SalesPeople&range=0  

In the first two cases, the aliases expression WHERE(Department:sales) filters perspective Person objects. In the third case, the expression filters Person objects connected to a perspective Message object via Sender.Person.

Outer WHERE filters can also be used in aggregate query metric functions and grouping parameters (described elsewhere).