> ## Documentation Index
> Fetch the complete documentation index at: https://docs.activeviam.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Database Queries

The guide will show you how to build and use the database queries.

## Get-by-key queries

### Running a simple get-by-key query

The get-by-key query retrieves the record corresponding to a key.<br />
For example, given a table `trades` where rows are indexed by their trade id, we can retrieve the trader that made the trade `1023`:

```java theme={"languages":{"custom":["/engine/python-sdk/6.2/languages/pycon.tmLanguage.json"]}}
final GetByKeyQuery query =
    databaseVersion
        .getQueryManager()
        .getByKeyQuery()
        .forTable("trades")
        .withTableFields("trader")
        .toQuery();
final IRecordReader record =
    databaseVersion.getQueryRunner().getByKeyQuery(query).withKey(1023).run();
if (record != null) {
  final var trader = record.read(0);
}
```

If the table has multiple key fields (e.g. `year`, `month`, and `day`), we can pass key values either as variadic
arguments or as a list:

```java theme={"languages":{"custom":["/engine/python-sdk/6.2/languages/pycon.tmLanguage.json"]}}
databaseVersion.getQueryRunner().getByKeyQuery(query).withKey(1998, 11, 21).run();
databaseVersion.getQueryRunner().getByKeyQuery(query).withKey(List.of(1998, 11, 21)).run();
```

You can find the list of key fields for a given store using `IDataTable#getKeyFieldNames`. Key values should be
passed to `withKey()` in this exact order.

### Compiling once, run twice

If a query will be run several times, it is better to compile it once:

```java theme={"languages":{"custom":["/engine/python-sdk/6.2/languages/pycon.tmLanguage.json"]}}
final IPreparedGetByKeyQuery query =
    database
        .getQueryManager()
        .getByKeyQuery()
        .forTable("trader")
        .withTableFields("trader")
        .compile();
```

And run it multiple times:

```java theme={"languages":{"custom":["/engine/python-sdk/6.2/languages/pycon.tmLanguage.json"]}}
for (int i = 0; i < 10; i++) {
  databaseVersion.getQueryRunner().getByKeyQuery(query).withKey(i).run();
}
```

### Using a get-by-key query

The get-by-key query returns the result as a RecordReader.
For example, we can create a service to retrieve the trader name for a trade id:

```java theme={"languages":{"custom":["/engine/python-sdk/6.2/languages/pycon.tmLanguage.json"]}}
private static class TraderNameService {
  IDatabase database;
  IPreparedGetByKeyQuery traderQuery;
  public TraderNameService(IDatabase database) {
    this.database = database;
    this.traderQuery =
        database
            .getQueryManager()
            .getByKeyQuery()
            .forTable("trader")
            .withTableFields("trader")
            .compile();
  }
  public String getTraderName(int tradeId) {
    final IRecordReader recordReader =
        database
            .getHead(IEpoch.MASTER_BRANCH_NAME)
            .getQueryRunner()
            .getByKeyQuery(this.traderQuery)
            .withKey(tradeId)
            .run();
    return (String) recordReader.read(0);
  }
}
```

## List queries

### Minimal list query

The minimal list query asks a table name and retrieve all records:

```java theme={"languages":{"custom":["/engine/python-sdk/6.2/languages/pycon.tmLanguage.json"]}}
final IPreparedListQuery query =
    database
        .getQueryManager()
        .listQuery() // the builder guides you and impose the steps order
        .forTable("trades")
        .withoutCondition()
        .selectingAllTableFields()
        .compile();
```

To run the query, use the QueryRunner of your database version:

```java theme={"languages":{"custom":["/engine/python-sdk/6.2/languages/pycon.tmLanguage.json"]}}
databaseVersion.getQueryRunner().listQuery(preparedListQuery).withoutParameters().run();
```

### Adding a condition

The list query can be filtered using a condition builder.

Let's retrieve the trades made by John:

```java theme={"languages":{"custom":["/engine/python-sdk/6.2/languages/pycon.tmLanguage.json"]}}
final IPreparedListQuery preparedListQuery =
    database
        .getQueryManager()
        .listQuery() // the builder guides you and impose the steps order
        .forTable("trades")
        .withCondition(BaseConditions.equal(FieldPath.of("trader"), "John"))
        .selectingAllTableFields()
        .compile();
```

#### Dynamic conditions

The condition can be parameterized at the query compilation, and its parameter values and the parameter
values specified at query execution.

Let's retrieve the trades made by Paul with that mechanism:

```java theme={"languages":{"custom":["/engine/python-sdk/6.2/languages/pycon.tmLanguage.json"]}}
final IPreparedListQuery preparedListQuery =
    database
        .getQueryManager()
        .listQuery() // the builder guides you and impose the steps order
        .forTable("trades")
        .withCondition(BaseConditions.equal(FieldPath.of("trader")).as("parameter"))
        .selectingAllTableFields()
        .compile();
databaseVersion
    .getQueryRunner()
    .listQuery(preparedListQuery)
    .withParameters(Map.of("parameter", "Paul"))
    .run();
```

### Selecting fields

If not all fields are useful, we can select the fields returned by the query.
There are 3 different ways of doing so.

#### Aliasing fields

This is the generic approach.
We can alias the fields returned by the query like in SQL queries:

```java theme={"languages":{"custom":["/engine/python-sdk/6.2/languages/pycon.tmLanguage.json"]}}
final IPreparedListQuery preparedListQuery =
    database
        .getQueryManager()
        .listQuery() // the builder guides you and impose the steps order
        .forTable("trades")
        .withCondition(BaseConditions.equal(FieldPath.of("trader"), "John"))
        .withAliasedFields(
            AliasedField.create("alias1", FieldPath.of("tradeId")),
            AliasedField.create("alias2", FieldPath.of("ToProduct", "currency")))
        .compile();
final var alias1Index =
    preparedListQuery.getQueryResultFormat().getPlainRecordFormat().getFieldIndex("alias1");
```

#### All fields belong to queried table

If all the fields come from the queried table, there is a shorter method that will use the field name as alias:

```java theme={"languages":{"custom":["/engine/python-sdk/6.2/languages/pycon.tmLanguage.json"]}}
.withAliasedFields(
    AliasedField.create("tradeId", FieldPath.of("tradeId")),
    AliasedField.create("date", FieldPath.of("date")))
```

This can be replaced by:

```java theme={"languages":{"custom":["/engine/python-sdk/6.2/languages/pycon.tmLanguage.json"]}}
.withTableFields("tradeId", "date")
```

#### Fields without alias

In case only the position (and not the alias) of the fields in the results matters, then the following is also possible:

```java theme={"languages":{"custom":["/engine/python-sdk/6.2/languages/pycon.tmLanguage.json"]}}
final IPreparedListQuery preparedListQuery =
    database
        .getQueryManager()
        .listQuery() // the builder guides you and impose the steps order
        .forTable("trades")
        .withCondition(BaseConditions.equal(FieldPath.of("trader"), "John"))
        .withFieldsWithoutAlias(FieldPath.of("tradeId"), FieldPath.of("ToProduct", "currency"))
        .compile();
```

### Adding a timeout or a result limit

For performance reasons, we can restrict the query by adding a result limit and/or a timeout.
The timeout will cancel the query if reached and an exception will be thrown.
The result limit set the maximal number of rows that will be returned.

```java theme={"languages":{"custom":["/engine/python-sdk/6.2/languages/pycon.tmLanguage.json"]}}
final IPreparedListQuery preparedListQuery =
    database
        .getQueryManager()
        .listQuery() // the builder guides you and impose the steps order
        .forTable("trades")
        .withCondition(BaseConditions.equal(FieldPath.of("trader"), "John"))
        .withTableFields("tradeId", "date")
        .withResultsLimit(1000)
        .compile();
databaseVersion
    .getQueryRunner()
    .listQuery(preparedListQuery)
    .withoutParameters()
    .withTimeout(Duration.ofSeconds(10L))
    .run();
```

### Adding query tags

Each query can have a map of tags to help monitoring the application.
For example, we can add the name of the operation:

```java theme={"languages":{"custom":["/engine/python-sdk/6.2/languages/pycon.tmLanguage.json"]}}
final IPreparedListQuery preparedListQuery =
    database
        .getQueryManager()
        .listQuery() // the builder guides you and impose the steps order
        .forTable("trades")
        .withCondition(BaseConditions.equal(FieldPath.of("trader"), "Paul"))
        .withTableFields("tradeId", "date")
        .withQueryTags(Map.of("operation", "trade-inspection"))
        .compile();
databaseVersion.getQueryRunner().listQuery(preparedListQuery).withoutParameters().run();
```

### Using a list query

The list query will return a `ICursor` that you can use to manipulate every record.
It is important to read through the `ICursor` within a try-with-resources statement to ensure that all related resources are freed at the end of the manipulation.

For example, we can retrieve the sum of "value" by date:

```java theme={"languages":{"custom":["/engine/python-sdk/6.2/languages/pycon.tmLanguage.json"]}}
public Map<Object, Double> getValueByDates() {
  final IPreparedListQuery query =
      database
          .getQueryManager()
          .listQuery()
          .forTable("trades")
          .withCondition(BaseConditions.in(FieldPath.of("City")).as("City"))
          .withTableFields("date", "value")
          .compile();
  final ICursor result =
      databaseVersion
          .getQueryRunner()
          .listQuery(query)
          .withParameters(Map.of("City", Set.of("Paris", "London")))
          .run();
  final Map<Object, Double> valuesByDates = new HashMap<>();
  try (result) {
    while (result.next()) {
      final Object date = result.getRecord().read(0);
      final double value = result.getRecord().readDouble(1);
      valuesByDates.merge(date, value, Double::sum);
    }
  }
  return valuesByDates;
}
```

## Distinct queries

The distinct query allows to retrieve all distinct values for a field or a field combination from a record list.
Record list could be filtered with some specified conditions.

Its characteristics are very similar to list query ones. It returns a `ICursor`, and provides following options:

* Field aliasing
* Limit
* Timeout
* Query tags

This sample query extracts all the counterparties john has traded with:

```java theme={"languages":{"custom":["/engine/python-sdk/6.2/languages/pycon.tmLanguage.json"]}}
final IPreparedDistinctQuery preparedDistinctQuery =
    database
        .getQueryManager()
        .distinctQuery() // the builder guides you and impose the steps order
        .forTable("trades")
        .withCondition(BaseConditions.equal(FieldPath.of("trader"), "John"))
        .withTableFields("counterParty")
        .withResultsLimit(1000)
        .withQueryTags(Map.of("operation", "trade-inspection"))
        .compile();
databaseVersion
    .getQueryRunner()
    .distinctQuery(preparedDistinctQuery)
    .withoutParameters()
    .withTimeout(Duration.ofSeconds(10L))
    .run();
```

### Note on joined projection fields

Projection fields belonging to a table other than the base table are joined with a left join.
This implies that default or null values can be present in the distinct result.