More About INNERJOIN

You can use the INNERJOIN operator to join “left” and “right” documents based on a common numerical field. INNERJOIN only returns matching documents from the “left side” of the query.

For example, if you have order and customer documents that both contain a customer_id join key field, you can perform queries such as:

order_price>42 INNERJOIN/customer_id (customer_name:john AND customer_firstname:doe)

This returns all orders (the left-side documents) for John Doe that had an order price greater than 42.

Important: INNERJOIN queries have a significant impact on search performance.

This page discusses:

When and How to Use INNERJOIN
Handling Documents in Multiple Slices

When and How to Use INNERJOIN

Only use INNERJOIN for complex multicriteria search on the right-side document. In the above example, this is the customer.

Ideally, for better search performance, aim instead to inline the right-side document information in the left-side document. However, INNERJOIN is mandatory for certain multicriteria queries.

Requirements

The field must be an integer, a date or a value, retrievable and RAM-based.
For a value field, the innerjoin does not support two different join key fields for left and right side. For details about value fields, see Create Value Facets for Nonhierarchical Metas.

Perform an INNERJOIN Query

When the join field name is the same for both left and right documents

UQL: (left-side query) INNERJOIN/field (right-side query)
ELLQL: #innerjoin(field, left-side query, right-side query)

When the join field name is different for the left and right documents (not applicable to value fields)

UQL: (left-side query) INNERJOIN/field1=field2 right-side query)
ELLQL: #innerjoin(field1,field2, left-side query, right-side query)

Important: When using a join field that was generated by a Data Model property, always specify the full field name, prefixed by the declaring class name, even in UQL.

Hide INNERJOIN from Users

To avoid exposing the INNERJOIN operator to the user, you can use query templates to combine ELLQL and UQL to perform the join from the user query. For details, see Defining Query Templates.

Handling Documents in Multiple Slices

The INNERJOIN operator is intra-slice only: you cannot join a left-side document (LD) from one slice to a right-side document (RD) in another slice.

There are two main ways of handling this, depending on the use case.

Use Case 1: LD and RD are shardable in the Same Way

For example, when indexing customers and orders, we can shard the index by customer ID, and make sure that all orders go to the same slice as the customer.

To do that, we must override the automatic slice balancing algorithm, using a PAPI directive called forcedSlice.

Note: A PAPI directive is a small value associated to a document, which is not part of the textual content, that gives processing instructions or hints to the processing chain.

Set a PAPI Directive

document.setCustomDirective(name, value);document.setCustomDirective("forcedSlice", "2");

You then must correctly balance all slices.

Use Case 2: LD and RD are not shardable in the Same Way

In this case, the only solution is to fully duplicate the right-side documents (RD) in each slice. As the RD is never retrieved by the INNERJOIN query, there is no risk of duplication.

This works best when the set of RDs is relatively small, so duplicating them does not dramatically impact the slices.

You need to push each RD N times (where N=number of slices), each time with a different URI, for example /myoriginaluri/slice3.

For each document, you need to set the forcedSlice directive as described in the Use Case 1: LD and RD are shardable in the Same Way.

You do not need to do anything special for your LD, since the automatic slice allocator keeps dispatching them.