Pair Parameter (OLAP)

[Table of Contents](https://github.com/dell-oss/Doradus/wiki/OLAP Databases: Table-of-Contents) | Previous | Next
OLAP Aggregate Queries: Pair Parameter

---

The *pair* parameter supports a special kind of aggregate query called a *dual role* query. To illustrate when it is needed, let’s first look at a dual role *object* query, which doesn’t require the "pair" functionality.

Assume we divide the participants of a Message object into senders and internal recipients. Suppose we want to find all messages where either:

1. The sender resides in the Kanata office and the internal recipient is a member of a Support department, or:

2. The internal recipient resides in the Kanata office and the sender is a member of a Support department.

In other words, we want to find messages between the Kanata office and a Support department, but we don’t care if the message is sent from Kanata to Support or the other way around. We also have to eliminate the case where one participant is both in Kanata and belongs to Support but the other participants are neither (otherwise it’s not really a query "between" these two roles.)

As an object query, this is rather straightforward and can be specified as follows:

.../Message/_query?q=(Sender.Person.Office:Kanata AND InternalRecipients.Person.Department:Support) OR
(InternalRecipients.Person.Office:Kanata AND Sender.Person.Department:Support)

This query uses two OR clauses: the first clause selects messages sent by someone in the Kanata office and received by anyone in the Support department; the second clause uses the same office/department criteria but reverses the sender/receiver roles. A message that satisfies either clause is selected.

Now suppose we want to execute this as an aggregate query. If all we want is a total count of messages, we can use the same query parameter as above.

But suppose we want to group the results based on a field belonging to one of the roles: for example, we want to group by the Department of the Kanata participant. When the Kanata participant is the sender, we want to group by Sender.MessageAddress.Person.Department. When the Kanata participant is the receiver, we want to group by InternalRecipients.MessageAddress.Person.Department. But we can only group on one field at each level—we can’t group on a participant’s role ("the Kanata participant"), which is defined by two different link paths.

The aggregate "pair" feature allows such dual role queries. To demonstrate how it works, below is the aggregate query proposed above, grouping messages by the Kanata participants’ department, regardless of whether those participants are senders or the internal recipients:

.../Message/_aggregate?pair=Sender,InternalRecipients
&q=_pair.first.Person.Office:Kanata AND _pair.second.Person.Department:Support
&f=_pair.first.Person.Department

The pair parameter works as follows:

• When used, the pair parameter must be a comma-separated list of exactly two link paths referring to the same table. These fields are referred to as the "pair fields". In this example, the pair fields are Sender and InternalRecipients, which both refer to the Participant table. Though not shown in this example, the pair field link paths can use WHERE filters if needed.

• When the pair parameter is used, the system field _pair can be used in the query and/or fields parameters. The _pair system field must be followed by the subfield first or second, and the remainder of the expression must be valid based on the pair fields’ table. In this example, since the pair fields are links to the Participant table, the remainder of the expression must be valid for Participant objects.

• The subfield name (first or second) is used for binding purposes: the _pair field expression is actually applied to both pair fields. That is, the query is executed twice with the roles of the pair fields reversed. In the first execution, the query expression is first evaluated using Sender in the first role and Recipients in the second role:

  Sender.Person.Office:Kanata AND InternalRecipients.Person.Department:Support

In this execution, the sender(s) are bound to _pair.first and the internal recipients are bound to _pair.second. Because the grouping parameter groups by _pair.first.Person.Department, this means that if the expression selects a message, the sender’s department is used to choose the message’s group. In other words:

&f=_pair.first.Person.Department

Is interpreted as:

&f=Sender.Person.Department

• The roles of the pair fields are then swapped and the query expression is executed again. The second query executed is:

  InternalRecipients.Person.Office:Kanata AND Sender.Person.Department:Support

Here, _pair.first is bound to Recipients and _pair.second is bound to Sender. Consequently, if the expression selects a message, it is recipient’s department that is used to choose the message’s group. In other words:

&f=_pair.first.Person.Department

Is interpreted as:

&f=InternalRecipients.Person.Department

In this example, "location in Kanata" is one role and “department in Support” is the other role. Messages are found by looking for one role as sender and the other role as internal recipients and then vice versa. Regardless of which combination selects a message, the aggregate results are grouped by the Kanata office’s department.

Note that when the pair parameter is used, the query and fields parameters can use WHERE filters and all other normal aggregate query features.

Pair queries can use standard metric functions and expressions including multiple metric parameters. Additionally, pair queries can reference the _pair subfields in metric expressions. For example:

GET /Email/Message/_aggregate?pair=Sender,InternalRecipients
    &q=_pair.first.Person.Office:Kanata AND _pair.second.Person.Department:Support
    &f=_pair.first.Person.Department
    &m=COUNT(_pair.first),COUNT(_pair.second)
    &range=0

This is the dual role query described so far, using a metric parameter (&m) that returns two totals: (1) the number of objects participating in the first role (Sender), and (2) the number of objects participating in the second role (InternalRecipients). Both metric computations are grouped by the department of the participant.

Note that using a _pair field in a metric parameter may produce unexpected results. If the same object participates in both roles, it included twice in each metric computation. In general, the objects selected by the two pair field parameters should be mutually exclusive.