EITCO Query Language 2.1.6

What is EQL?

The EITCO query language provides a unified interface for searches. Hence, this documentation addresses developers, mainly those who use java (or any JVM compatible language). However, there is also a port for Typescript.

EQL is agnostic of the actual implementation of the search. While still a lot of searches are made on classical relational databases, more and more are executed on NoSQL databases. The language EQL is independent of the actual search implementation, however, there are some specifics that will only work for specific search implementations. Currently, there are implementations for SQL, in-memory searches and Solr.

There are several ways to search using EQL. All have a common entry point: de.eitco.commons.query.language.api.SearchService.

Fluent API

The interface SearchService contains three entry points using a Fluent API:

Fluent API entry points
    @NotNull
    PageDefinitionStep<EntityType> all();

    @NotNull
    ConditionBuilder<PageDefinitionStep<EntityType>, ?, ?, ?, ?, ?, ?, ?> where();

    @NotNull
    ConditionBuilder<Boolean, ?, ?, ?, ?, ?, ?, ?> existsWhere();

The first method all() simply returns all elements - paging still needs to be defined.

The second method where() begins the fluent api for creating a condition by returning a ConditionBuilder. The resulting search will return only elements that meet the given condition. Paging will be need to be defined.

The third method existsWhere() also starts the fluent api for creating a condition. As opposed to where(), it will create a query that determines whether at least one element that meets the condition exists.

Conditions

The simplest conditions are boolean literals. Whenever you have a ConditionBuilder at hand you can simply call alwaysTrue() or alwaysFalse() to create a condition that always respectively never holds. Thus, the following code would be equivalent to simply calling all():

Simple fluent API call
            service.where().alwaysTrue()

Comparisons and Values

The first less simple conditions are comparison. EQL allows values to be compared with the operators =, ≠, <, ≤, > and ≥. These can be created using the methods equalTo(), notEqual(), lessThan(), lessEqual(), greaterThan() and greaterEqual(). These are found in the class PredicateInfixBuilder for reference. There are some more specific comparison operators, but we will keep it simple for now.

Values can be defined using the method value(Object). The following code would still be equivalent to simply calling all()

Simple comparison
            service.where().value(1).equalTo().value(1)

Context References

In order for conditions to actually do something we need to reference properties of the objects we search for. What a property is and which properties exist depends on the search implementation. When using the in-memory search on a list of java objects for example, every field of the class of the objects searched for would be able to be referenced. In a sql search on a table the fields of the table could be referenced.

A context reference is a list of strings defining a path to the property referenced. For in-memory searches this allows to evaluate properties by reflection and even reference properties of objects that are a field of the objects searched for themselves. For sql searches this allows to specify alias prefixes and the like.

Condition using a context reference
            service.where().contextReference("my", "property").lessThan().value(400)

The example above would result - in case of a sql search - in a sql WHERE my.property < 400. In case of an in-memory search it would assume a class like the following:

A simple class that can be searched with the statement above
public class Outer {

    private Inner my;

    public static class Inner {

        private int property;

        // ...
        // other properties, getters, setters, etc
    }
    // ...
    // other properties, getters, setters, etc
}

The statement given defines a condition that holds for any instance of Outer whose field my holds an instance of Inner whose field property is less than 400.

Ending a condition with holds()

After you specify the condition call holds() to specify paging:

A simple complete condition ready for paging
            service.where().contextReference("my", "property").lessThan().value(400).holds()
Boolean Operators

Comparisons can be linked to other comparisons with boolean operators. After a comparison one can call and() or or() and then specify another condition in order to create a condition that holds if and only if both conditions (respectively at least one of the conditions) hold(s).

Conditions linked with and()
            service.where().contextReference("my", "property").lessThan().value(400).and()
                .value("my-value").notEqual().contextReference("other-property")

This way you can specify very complex queries:

Complex query using many and() and or() calls
            service.where().contextReference("my", "property").lessThan().value(400).and()
                .value("my-value").notEqual().contextReference("other-property").or()
                .contextReference("field").notEqual().value("value").or()
                .contextReference("my", "property").greaterEqual().value(600).and()
                .contextReference("field").equalTo().value("more")

This rises the question, in which order the and/or links are evaluated. It is done as most conventions specify: and() links are evaluated before or() links. Thus, the condition above would result in a sql clause equivalent to the following:

An SQL WHERE clause with explicit braces
WHERE
    ("my"."property" < 400 AND 'my-value' != "other-property") OR
    ("field" != 'value') OR
    ("my"."property" >= 600 AND "field" = 'more')

Should one need to set braces explicitly one can use the and(Expression<Boolean>) and or(Expression<Boolean>) methods.

Setting braces with and(Expression<Boolean>)
            service.where().contextReference("my", "property").lessThan().value(400).and(
                Eql.condition()
                    .contextReference("field").equalTo().value("value-1").or()
                    .contextReference("field").equalTo().value("value-2").or()
                    .contextReference("field").equalTo().value("value-3").holds()
            )

The example above would create in the following sql clause

An SQL WHERE clause with explicit braces
WHERE
    "my"."property" < 400 AND (
        ("field" != 'value-1') OR
        ("field" != 'value-2') OR
        ("field" != 'value-3')
    )

Of course EQL also supports boolean not. Before any EQL statements that result in a condition you can simply call not() to negate the given condition:

Negating a condition with not()
            service.where().not().contextReference("my", "property").lessThan().value(400)

Paging

As soon as you finish a condition with holds() or if you just called SearchService.all() you will need to specify paging. The results of searches on huge amount of data can be huge as well, so SearchService supports paging that is returning only a part of the result.

Of course, if you know that the result is that huge, you can simply skip paging by calling unpaged() which will simply return all elements that satisfy the given condition.

An unpaged query
            service.where()
                .contextReference("my", "property").lessThan().value(400)
                .and().contextReference("field").notEqual().value("value-4").holds()
                .unpaged()

Otherwise, you will need to page the result. Paging is underspecified without ordering, so at first you will need to define an order with order(). You can rely on the implicit order of the search implementation, by just skipping to the next step. Otherwise, you can give fields to order by ascendingly and descendingly by calling ascendingBy(String…​) and descendingBy(String…​). The strings given to these methods hold the names of the properties that should be ordered by just like context references. Both methods can be called several times.

After the order is specified the page can be defined. It consists of its position in the complete result and its size. The position is given with the method from(int). After that the size can either be specified by pageSize(int) or by to(int). While the first method directly sets the size of the page, the second specifies the index of the last element. Thus calling from(n).pageSize(m) would be equivalent to from(n).to(n + m). Specifying the page can also be omitted, resulting in an unpaged but ordered result.

A completely specified paging
            service.where()
                .contextReference("my", "property").lessThan().value(400)
                .and().contextReference("field").notEqual().value("value-4").holds()
                .order() (1)
                .ascendingBy("my", "property") (2)
                .descendingBy("field") (3)
                .from(10). (4)
                pageSize(400) (5)
1 The result should be ordered …​
2 …​ primarily ascending by the property "my.property" …​
3 …​ if my.property equals for two elements, those should be ordered descending by the property "field"
4 The result should not contain the first 10 elements …​
5 …​ and only the 400 that follow (or less if the result does not contain that many elements)

Finishing a Query

There are several ways to finish a query. The completely specified paged example is one. That call results in an instance of Page<T>, which contains a member result of Type List<T> that contains the elements queried for, in the order specified. The page also contains a page definition, which is the one that was given so that the result also contains its 'context'. Additionally, the complete count is returned. This is not necessarily the size of the result, but it is what its size would be if the query was unpaged.

Above we saw a call to unpaged() which completely omitted paging. In this case the query is executed with this call and a list with the result is returned. There is no need to return a page in this case, since the call is unpaged.

A common use case are queries whose condition identifies a unique feature of the elements so that the query has at most one result element. Searches for a given id are an example. In this case the method uniqueResult() can be called, where unpaged() could be called:

A query by id
            service.where().not().contextReference("id").equalTo().value(554898L).holds()
                .uniqueResult()

This method returns an Optional<Type> which is empty if no element matches the given condition, and the unique element that matches the condition otherwise. If several elements match the condition, an exception is thrown. In this case no paging can be specified.

Another way to finish a query is to call count(). This also omits paging. However, it does not return a result list only the number of elements that match the given condition.

A query that only counts the matching elements
            service.where()
                .contextReference("my", "property").lessThan().value(400)
                .and().contextReference("field").notEqual().value("value-4").holds()
                .count()

Iterating the Result Set

Using the method iterable(int) one can create an iterable of the result set. The given integer sets the page size. The resulting iterable will iterate over the complete result set, but obtain the results in pages of the given size.

The result as iterable
            for (var element : service.where()
                .contextReference("field").notEqual().value("value-4").holds()
                .order().descendingBy("field")
                .iterable(500)) {

                System.out.println(element.toString());
            }

The code above will iterate over every element that does not have the value "value-4" in its field field in descending order by that field and prints its string representation to stdout. Every 500 result elements it will retrieve the next 500 elements and go on iterating, until none else are left.

The API can also be used to create a stream:

The result as stream
            service.where().contextReference("field")
                .notEqual().value("value-4").holds()
                .order().descendingBy("field")
                .stream(500)

This will create a stream of all the elements, that do not have the value "value-4" in its field field (in descending order by that field). When items of this stream are actually retrieved, they will be in chunks of the size 500.

Thus, the code below is equivalent to the iterating example, above.

Iterating over a retrieved stream
            service.where().contextReference("field")
                .notEqual().value("value-4").holds()
                .order().descendingBy("field")
                .stream(500)
                .map(Object::toString).forEach(System.out::println);

Low-level API

As one can see, the interface SearchService has a lot of default methods but only on abstract method:

The method search()
    @NotNull
    Page<EntityType> search(@NotNull SearchRequest searchRequest);

The method is given a SearchRequest and retrieves a Page<>.

The parts of a search request
    private final PageDefinition pageDefinition;
    private final Expression<Boolean> filter;
    private final SearchOptions options;

The searchOptions can be used to specify detailed behaviour.

Page Definition

The page definition contains information about the page of the result to retrieve. It can be created using its constructor and specifying, offset, size and order directly - but analogously to the fluent approach above:

Two ways to specify a page
            new PageDefinition(200, 400, List.of(
                new Order(OrderDirection.DESCENDING, new ContextReference<String>(List.of("field"))))); (1)

            service.all()
                .order().descendingBy("field").from(200).pageSize(400) (2)
1 creating a page definition directly by calling the constructor
2 defining the same page definition using the fluent api

Filtering

Using the filter property one can specify a condition that every element of the result must meet.

The simplest condition is a boolean literal:

Boolean literal
            new BooleanLiteral(true)

This will create the condition that always holds.

comparisons and values

In order to create more complex conditions the classes ContextReference<>, Value<> and Equality<> can be used:

Boolean literal
            new Equality<>(new ContextReference<>(List.of("my", "property")), new Value<>(400))

This would create the following sql WHERE-clause

WHERE "my"."property" = 400

For other comparison operators the classes GreaterThan, LessThan, LikeExpression, StartsWithExpression and Contains are available.

boolean operators

Conditions can be linked by the boolean operators "and" and "or" using the classes AndExpression and OrExpression. The negation is provided by NotExpression.

A complex query using the low level api
            new OrExpression(
                new AndExpression(
                    new LessThan<>(
                        new ContextReference<>(List.of("my", "property")),
                        new Value<>(400)
                    ),
                    new NotExpression(
                        new Equality<>(
                            new Value<>("my-value"),
                            new ContextReference<>(List.of("other-property"))
                        )
                    )
                ),
                new OrExpression(
                    new NotExpression(
                        new Equality<>(new ContextReference<>(List.of("field")), new Value<>("value"))
                    ),
                    new AndExpression(
                        new GreaterThan<>(new ContextReference<>(List.of("my", "property")), new Value<>(600)),
                        new Equality<>(new ContextReference<>(List.of("field")), new Value<>("more"))
                    )
                )
            )

This would result in the following WHERE-clause

WHERE
    ("my"."property" < 400 AND NOT 'my-value' = "other-property") OR
    (NOT "field" = 'value') OR
    ("my"."property" > 600 AND "field" = 'more')

So, given a SearchService one can use the low level api to query a result page as follows:

Complete low level search example
            Page<?> result = service.search(new SearchRequest(
                new PageDefinition(
                    200,
                    400, List.of(new Order(OrderDirection.DESCENDING, new ContextReference<String>(List.of("field"))))
                ),
                new NotExpression(new Equality<>(new ContextReference<>(List.of("my", "property")), new Value<>(400))),
                SearchOptions.DEFAULT
            ));

This will retrieve elements whose property my.property does not have the value 400, ordered by the field field. The first 200 elements will not be returned and only the 400 following will (should they exist).

Solr EQL

Solr EQL Usage

If you want to use the Solr EQL visitor, there are two ways to do this. If you want to search in an existing Solr you can use 1). If you want to get a Solr query string you can use 2).

1) With SolrSearchService

        SolrSearchService searchService = new SolrSearchService(solrClient, Map.of());
        Expression<Boolean> expression = Eql.condition().contextReference("id").equalTo().value(47).holds();

The first thing you will need is a SolrClient (from SolrJ). You also need a SolrSearchService. The SolrSearchService needs two parameters. The first parameter is the previously created SolrClient and the second parameter is an optional map of information about the current schema in the solr collection for the query. Finally, you will need an EQL Expression. In chapter Solr EQL Expressions the individual Solr Expressions are presented. Whenever you create the queries above, you can create a SearchRequest and put the variables in the instantiation. Moreover, you can add a PageDefinition. In this example we used PageDefinition.oneElement().

        SearchRequest searchRequest = new SearchRequest(PageDefinition.oneElement(), expression, queryExtension);

2) With <Expression>.get.accept:

    eqlElement.get().accept(new ToSolrEqlElementVisitor(schema), null)

In this way you get the search string out of the EQL Expression. You can add the Visitor to the accept method. This will return the result search string.

Solr EQL Expressions

Calling equal to:

The input value must be matched exactly in the search result.

                Eql.condition()
                    .contextReference("text").equalTo().value("value")
                    .holds(),

Returns:

                "text:\"value\"",
Calling like:

You can add a wildcard to the input value. In the EQL you can use two different wildcards for example ? for a single character or * for multiple characters. The result is based on the wildcard you are defined in the input value.

                Eql.condition()
                    .contextReference("text").like().value("test*")
                    .holds(),

Returns:

                "text:test*",
Calling contains:

The input value must be somewhere in the search result. Also, you can find a string in an array list.

                Eql.condition()
                    .contextReference("text").contains().value("test")
                    .holds(),

Returns:

                "text:test",
Calling in:

The input value can contain multiply entries (array). The result gets the results out of the input array based on the field.

                Eql.condition()
                    .contextReference("text").in().values("A", "B", "C")
                    .holds(),

Returns:

                "text:(A || B || C)",
Calling and or or:

Connect two Expression with and.

                Eql.condition()
                    .contextReference("text").contains().value("test")
                    .and(Eql.condition().contextReference("other").equalTo().value("value").holds())
                    .holds(),

Returns:

                "text:test && other:\"value\"",
Calling greater than:

The result is bigger than the input value.

                Eql.condition()
                    .contextReference("field").greaterThan().value("value1")
                    .holds(),

Returns:

                "field:[value1 TO *]",
Calling less than:

The result is smaller than the input value.

                Eql.condition()
                    .contextReference("field").lessThan().value("value1")
                    .holds(),

Returns:

                "field:[* TO value1]",
Calling between:

The result is between two input values without the left outermost and right outermost result.

                Eql.condition()
                    .contextReference("column1")
                    .between().value(1).value(10)
                    .holds(),

Returns:

                "column1:{1 TO 10}",
Calling between inclusive:

The result is between two input values with the left outermost and right outermost result.

                Eql.condition()
                    .contextReference("column2")
                    .betweenInclusive().value(200).value(500)
                    .holds(),

Returns:

                "column2:[200 TO 500]",
Calling between right inclusive:

The result is between two input values with the right outermost and without the left outermost result.

                Eql.condition()
                    .contextReference("column3")
                    .betweenRightInclusive().value(111).value(999)
                    .holds(),

Returns:

                "column3:{111 TO 999]",
Calling between left inclusive:

The result is between two input values with the left outermost and without the right outermost result.

                Eql.condition()
                    .contextReference("column4")
                    .betweenLeftInclusive().value("A").value("Z")
                    .holds(),

Returns:

                "column4:[A TO Z}",

Solr Hit Score

In Solr you can create a filter query with a hit score. The hit score is calculated from the number of requests linked with and or or.

Example:

You have three entries in your Solr collection in the field "testfeld"

Entry Field Content

1

testfeld

Arveo EQL Test

2

testfeld

Arveo EQL 1

3

testfeld

Arveo EQL 2

If you now use the following query:

Eql.condition()
.contextReference("testfeld").contains().value("Arveo")
.or(Eql.condition().contextReference("testfeld").contains().value("EQL").holds())
.or(Eql.condition().contextReference("testfeld").contains().value("Test").holds())
.holds()

All three entries would be returned as a result. Entry 2 and 3 would have a hit score of 2.0 and the 1st entry would have a hit score of 3.0.

If you are using the EQL QueryExtensions, a hit score can be specified that must be met for the result. In the example we defined a hit-score of 1.0 for the minimum.

Set hit score:
        SolrHitScoreQueryExtension queryExtension = new SolrHitScoreQueryExtension(1.0);

Returns:

{!frange l=1.0}query($q)

Solr Highlighting

In Solr you can create a highlighting query extension. The highlighting will be added at the end of the query string.

Set acl as solr filter:
List<QueryExtension> queryExtensions = new ArrayList<>();
queryExtensions.add(new SolrHighlightingQueryExtension(500));
SearchRequest searchRequest = new SearchRequest(PageDefinition.oneElement(), expression, solrSearchOptions, queryExtensions);

Returns:

&hl=true&hl.method=unified&hl.fragsize=500&hl.tag.pre=<em>&hl.tag.post=</em>

More detail information about the highlighting you can find at the following at this url https://solr.apache.org/guide/8_11/highlighting.html.

ACL filter extension

The ACL filter extension uses a plugin that extends Solr with ACL checking functionality. The plugin’s documentation can be found in the Access Control Service documentation.

The extension makes it possible to define a filter query based on a minimum ACL right the user must have to be able to find the documents.

Example filter query
{!aclright userId=111 aclAssignmentId=222 granted=3}

The extension is used as shown in the example below:

Usage of the ACL filter extension
Expression<Boolean> expression = Eql.condition().contextReference("id").equalTo().value(47).holds();
SolrAclQueryExtension aclQueryExtension = new SolrAclQueryExtension(111L, 222L, (short) 3);

SearchRequest searchRequest = new SearchRequest(PageDefinition.oneElement(), expression, aclQueryExtension);
searchService.search(searchRequest);

JOOQ

The eql-jooq module provides an EQL based SearchService that can be used with JOOQ. Basically, the SearchServices uses a JOOQ DSLContext to provide query results containing Record items.

The JooqSearchService can be instantiated using a builder pattern as shown in the following example:

Instantiation of a JooqSearchService
JooqSearchService<Record, Object> searchService = JooqSearchService.buildRecordSearchService()
    .select()
    .fields(DSL.asterisk()) (1)
    .fromTable(DSL.table(DSL.name("patients")))(2)
    .withContext(dslContext)(3)
    .defaultEqlTranslation()
    .build();
1 The list of fields to select or the asterisk.
2 The table to use for the queries. Could be a single table or a join.
3 A configured DSLContext instance

The second type parameter (Object) of the search service results from the input type of the visitor implementation used to transform an EQL quer to a JOOQ query and is not significant for the usage of the SearchService. Instead of a generic table (as used in the example), a table generated by the JOOQ code generator can be used, too.

Once the service was instantiated, it can be used to perform queries as shown below:

Usage of the search service
Optional<Record> patient_id = searchService.where()
    .contextReference("patient_id")
    .equalTo()
    .value(738)
    .holds()
    .uniqueResult();

The search service provides the same fluent API as any EQL SearchService. The EQL expression formulated using the API will be translated to a JOOQ query.