Package com.smartgwt.client.docs.serverds

# Class AdvancedCriterionSubquery

java.lang.Object
com.smartgwt.client.docs.serverds.AdvancedCriterionSubquery

public class AdvancedCriterionSubquery extends Object
A specialized subclass of `DSRequest` that you use to declare the properties of a subquery (a `fieldQuery` or `valueQuery`) to be used in `AdvancedCriteria`.

This class is not meant to be created and used, it is actually documentation of settings allowed in a DataSource descriptor (.ds.xml file), for use with Smart GWT Pro Edition and above. See `com.smartgwt.client.docs.serverds` for how to use this documentation.

Subquery definitions are often very compact; only a few properties are permitted for a client-driven subquery (see "Restrictions on client-driven subqueries", below), and many use cases can be satisfied specifying just a `dataSource` and a `summary function`.

Criteria subquery definitions fall into two broad categories:

- Aggregation, where the subquery uses a `SummaryFunctionType` to aggregate or summarize a related dataset, and then filter on that aggregated or summarized value. For example, orders with more than 10 lines, customers with an average order value more than $1000, UK customers with an outstanding payment more than a week old, etc
- Related value, where the subquery selects a value (or, for `inSet`-type clauses, a set of values) from a related dataSource and then filters on that value. For example, Products that were not ordered last month, Employees who are based in one of the North American offices, Orders that include a particular category of Product, etc

In ideal circumstances - when both main and subquery `dataSource`s are `SQL DataSources`, and a number of other restrictions are satisfied - subqueries are implemented by incorporating their functionality into a larger overall SQL query, because this is the most efficient thing to do, and gives the best performance. See `canEmbedSQL` for a description of the rules and nuances around this.

In cases where we cannot implement subqueries by embedding SQL, they are implemented by converting the subquery definitions into separate real `DSRequest`s, executing them, and then combining their results into the wider resultset.

### Subquery Overview

Subqueries are used to derive a value to be compared as part of the main query, from a set of related data where there is no way to directly join to a single record (usually because the subquery is summarizing or extracting from a larger dataset). Each subquery must return a single value per record in the main query, because it going to be compared to a single value from each main record (this value either comes directly from the main record, or is derived via a `fieldQuery`)

Because the nature of a subquery is that it is deriving a single value from multiple records, subqueries are often involved in some kind of aggregation (returning a record count, or the minimum value, for example). While this is the most common use of subqueries, it is not the only one - see the "Simple valueQuery" example below.

Whether or not a subquery is aggregating multiple records, it typically needs to be constrained by a join to the outer query. For example, if you were looking for all customers that have placed more than $1000-worth of orders, you would use something like the "Simple aggregating subquery" below. As you will see, that example displays no explicit means of joining the subquery to the outer query, and yet one is obviously necessary because we are looking for the sum of "lineValue" customer-by-customer, not the total sum for all customers.

This join from the subquery to the outer query is applied implicitly by Smart GWT's SQL engine. It auto-discovers a Relation between the main dataSource and subquery dataSource by following `foreignKey` definitions from the subquery dataSource, until it finds a path to the main dataSource. This auto-discovery usually does the right thing, but there are two possibilities it does not address:

- There is no direct or indirect Relation from the subquery dataSource to the main dataSource. This is an unusual case for a subquery - most use use cases call for related dataSources - but valid cases are conceivable. To cope with this scenario, you set `queryFK` to the special value "`*none*`". Note, if Smart GWT fails to find a path from the subquery dataSource to the main dataSource and `queryFK:"*none*"` has not been set, the framework will log a warning and the fetch will fail
- There is more than one direct or indirect Relation from the subquery dataSource to the main dataSource, and you want to use a different one from the one that was auto-discovered. Again, the solution is to explicitly name the `queryFK` - see the documentation for that property for details of the syntax

Since Smart GWT can usually only make use of a single value as output from a subquery (see the section "Subqueries with inSet criteria" below for an important caveat), if your subquery returns multiple records, we will simply use the first. If the query returns multiple fields, you can specify the field to use as the subquery output with `queryOutput`. If the subquery returns more than one field and no `queryOutput` is specified, we will use the first aggregated (`summaryFunction`) field, or the first `grouped` field if there are no aggregated fields, or the `primaryKey` field if there are also no `groupBy` fields, or just the first numeric field failing all else. Note, however, that although we attempt to derive a sensible value, it never makes sense to return multiple records from a subquery, and it only really makes sense to return multiple fields if you explicitly identify the correct field with `queryOutput`. Ideally, for the sake of clarity, return a single record, and either have that record contain a single field, or specify `queryOutput`

#### Restrictions on client-driven subqueries

For security reasons, subqueries in `requests` that came from the client-side are only permitted to specify a handful of properties. These properties are sufficient to allow the full power of the subquery feature to be used, without allowing any of the much broader set of general properties associated with the `DSRequest` superclass. The properties that can be set in a client-driven subquery are just those that are documented as direct properties of the `AdvancedCriterionSubquery` class - specifically `canEmbedSQL`, `criteria`, `dataSource`, `groupBy`, `operationId`, `queryFK`, `queryOutput`, and `summaryFunctions`

For `DSRequest`s that originally came from the server, it is possible to have a subquery that specifies any `DSRequest` property. Many of these would only have any relevance or effect if the subquery was run separately rather than embedded (as described above and in the `canEmbedSQL` doc). If you need to do this, look in the server Javadoc for `DSRequest.setAllowArbitrarySubqueries(boolean)`

Finally, note that it is possible to switch off the ability to use subqueries altogether, either `per-DataSource`, or globally by setting the `allowCriteriaSubqueries` flag in your `server.properties` file:

      allowCriteriaSubqueries: false

#### Subqueries with inSet criteria

As noted above, subqueries nearly always need to return a single value, since that value is going to be used instead of a literal scalar value in record-by-record comparisons. The exception to this rule is when you are specifying a `valueQuery` with the `inSet` operator. In this case, you are saying that the field value must be one of a set of valid values, so in this one case it is appropriate and correct for the `valueQuery` to return a list of values rather than a single value. Note, we are still comparing a single field to the values in the list, so it should be a list of simple values (strings, numbers, booleans or dates, as appropriate), NOT a list of records

#### `exists / notExists` operators (SQLDataSource only)

The ``exists / notExists`` operators provide a concise way to filter records based on the existence (or absence) of related rows.

When both main DataSource and subquery DataSource are instances of `SQLDataSource`, these operators are transformed into `EXISTS (...)` / `NOT EXISTS (...)` correlated subselects in the generated SQL. Non-SQL backends, as well as when `canEmbedSQL:false` is set for SQL backends, will execute subqueries as separate `DSRequests` and apply their results manually.

These operators must be used with a `fieldQuery`:

      // EXISTS: at least one related row matches the subquery
      { operator: "exists",
        fieldQuery: { dataSource: "OrderLine", criteria: { ... } }
      }

See full examples in the "Examples" section below.

### Examples

Subqueries are quite hard to describe in narrative text, but the following examples demonstrate their use and should make things clearer

#### Simple aggregating subquery

This example uses a `fieldQuery` to select all Order records for customer 1234, where the order total (sum of all the order line values) is greater than $1000.

      Order.fetchData({
          _constructor: "AdvancedCriteria", operator: "and", criteria: [
              {fieldName: "customerNumber", operator: "equals", value: 1234},
              {
                   fieldQuery: {
                       dataSource: "OrderLine",
                       summaryFunctions: {lineValue: "sum"}
                   }, operator: "greaterThan", value: 1000
              }
          ]
      });

#### Simple valueQuery

This example uses a `valueQuery` to derive the employeeNumber of a manager when we only know the email address of that manager, so we can find out who reports to her. This example shows how to use additional criteria within a subquery. It also demonstrates the relatively rare situation where no join to the outer dataSource is required (hence the declaration of `queryFK: "*none*"`)

      Employee.fetchData({
          _constructor: "AdvancedCriteria", operator: "and", criteria: [
              {
                  fieldName: "reportsTo",
                  operator: "equals",
                      valueQuery: {
                          dataSource: "Employee",
                          queryFK: "*none*",
                          criteria: { operator: "and", criteria: [
                              {fieldName: "email", operator: "equals", value: 'mpatterson@classicmodelcars.com'}
                          ]
                      }
                  }
              }
          ]
      });

#### More complex aggregation example

This example uses both a `fieldQuery` and a `valueQuery` to select all US-based customers who placed more orders in 2022 than they placed in 2021. Note, this is the number of orders, not the value, and the particular field that we add the "count" function to is not important (count is the same regardless of which field you count). We chose the orderNumber in this case, but that choice is arbitrary

      Customer.fetchData({
          _constructor: "AdvancedCriteria", operator: "and", criteria: [
              {fieldName: "country", operator: "equals", value: "USA"},
              {
                  fieldQuery: {
                      dataSource: "Order",
                      summaryFunctions : { orderNumber : "count" },
                      criteria: {fieldName: "orderDate", operator: "iBetweenInclusive",
                                              start:new Date("2021-01-01"), end:new Date("2021-12-31")}
                  },
                  operator: "lessThan",
                  valueQuery: {
                      dataSource: "Order",
                      summaryFunctions : { orderNumber : "count" },
                      criteria: {fieldName: "orderDate", operator: "iBetweenInclusive",
                                              start:new Date("2022-01-01"), end:new Date("2022-12-31")}
                  }
              }
          ]
      });

#### `exists` / `notExists` operators

Show customers with any Orders in 2024 (exists).

      Customer.fetchData({
        _constructor: "AdvancedCriteria", operator: "and", criteria: [
          { operator: "exists",
            fieldQuery: {
              dataSource: "Order",
              criteria: { fieldName: "orderDate", operator: "iBetweenInclusive",
                          start: new Date("2024-01-01"), end: new Date("2024-12-31") }
            }
          }
        ]
      });

Products not ordered last month (notExists).

      Product.fetchData({
        _constructor: "AdvancedCriteria", operator: "and", criteria: [
          { operator: "notExists",
            fieldQuery: {
              dataSource: "OrderLine",
              criteria: { operator: "and", criteria: [
                { fieldName: "orderDate", operator: "iBetweenInclusive",
                  start: startOfLastMonth, end: endOfLastMonth }
              ]}
            }
          }
        ]
      });

- ## Field Summary

  Fields
  Modifier and Type
  Field
  Description
  `Boolean`
  `canEmbedSQL`
  Indicates whether this subquery can be implemented by embedding SQL directly in the wider SQL statement that will be used to resolve the fetch request.
  `Criteria`
  `criteria`
  Optional criteria to use for this subquery.
  `String`
  `dataSource`
  The name of the `DataSource` to use for this subquery
  `String`
  `queryFK`
  The default for a subquery is that the records are joined to the main DataSource on the first `foreignKey` one-to-many relationship found, in field order.
  `String`
  `queryOutput`
  The name of the field that will be used as the output of this query.

- ## Constructor Summary

  Constructors
  Constructor
  Description
  `AdvancedCriterionSubquery()`

- ## Method Summary

  ### Methods inherited from class java.lang.Object

  `clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait`

- ## Field Details

  - ### queryOutput

    public String queryOutput
    The name of the field that will be used as the output of this query. Only useful if your subquery returns more than one field, and optional even in that case. See the `Subqueries overview` for more details

    Default value is null

    See Also:
      - `Advanced Filtering`

  - ### queryFK

    public String queryFK
    The default for a subquery is that the records are joined to the main DataSource on the first `foreignKey` one-to-many relationship found, in field order. So for example, querying `Customer`s with a subquery on `Order`s, we discover `Order.customerId` FK pointing to `Customer.id`. More complicated relations are also auto-discovered: for example, querying `Customer`s with a subquery on `OrderLine`s, we will discover the link to `Customer` via `OrderLine.orderNumber` and then `Order.customerId`.

    For many use cases, there is only one relation between any two DataSources, so this default discovery of the relation is often sufficient. However, if you need to, you can optionally specify a `queryFK` as the fieldName of a `foreignKey` from the "many" to the "one". If the relation is indirect - as with the join from `OrderLine` to `Customer` described above - you can specify `queryFK` as a dot-separated path from "many" to "one", like this: `queryFK: "orderNumber.customerId"` (but note that this is optional - often, a start point is all that is required to identify the relation-path to use)

    NOTE:The `queryFK` property leverages the `includeVia` feature, and so is constrained by the same restrictions as that feature. Primarily, this means that `queryFK` can only name a `foreignKey` field on the `subquery dataSouce`; it is not valid to name a field on the main dataSource. In practice, this is not usually a restriction because typically the subquery dataSource will be at the "many" end of a direct or indirect relation to the main dataSource, and so is naturally the one that declares the `foreignKey`

    You can also specify the special value "*none*" for `queryFK`, which means the aggregation query should be done independently from the main query, to simply produce a value separately without any joins to the main dataset. A subquery that specifies `queryFK: "*none*"` is quite unusual because most use cases of subqueries require a join to the parent dataSource; however, valid use cases do exist - see the "Simple valueQuery" example on the `subquery overview page`

    Default value is null

    See Also:
      - `Advanced Filtering`

  - ### criteria

    public Criteria criteria
    Optional criteria to use for this subquery. Note, subqueries usually have implicit criteria, because they are joined to the main dataSource fetch unless the `queryFK` is set to the special value "*none*".

    Also, because subqueries are themselves part of a larger set of criteria, the subquery result is compared to some other value in that larger criteria - it is easy to get confused by this multi-level criteria issue. You may find it helpful to think of this property as "inside criteria" (criteria used inside this subquery to derive its result) and the criterion of which the subquery is a component (as a `fieldQuery` or `valueQuery`), as "outside criteria"

    Default value is null

    See Also:
      - `Advanced Filtering`

  - ### canEmbedSQL

    public Boolean canEmbedSQL
    Indicates whether this subquery can be implemented by embedding SQL directly in the wider SQL statement that will be used to resolve the fetch request. This property is considered as part of a set of rules we apply to decide whether a subquery can be resolved by embedding SQL (which is the most efficient thing to do, and may be significantly faster), or if we must resolve by separating the subquery out into its own `DSRequest` and manually applying the results.

    There are two reasons why we might decide to run a subquery as its own `DSRequest`: First, if either the "main" dataSource or the subquery dataSource is not an `SQL dataSource`, then obviously we cannot merge into a single SQL statement. More subtly, if there is the possibility that customized logic might be in play, we run the subquery separately because in that case we may interfere with - or completely fail to apply - the custom logic if we do not run the subquery as a full-fledged `DSRequest`.

    `canEmbedSQL` is intended to allow you to override our heuristics, for cases where you know a particular subquery definitely can, or definitely can't, be embedded.

    Note, whether or not we separate out the subquery into its own `DSRequest`, `declarative security rules` are always applied. So, for example, if the current user is not permitted to use the `subquery dataSource` or an `operation` explicitly named on the subquery, the entire fetch operation that the subquery is part of will fail. More nuanced, if the current user is prohibited by `field-level security` from viewing a field, that field will be stripped out of the subquery, which may lead to the subquery failing in a hard-to-predict way (but note, we always log a warning to the server logs when a field is removed in these circumstances).

    The rules around embedding or separating a subquery are, in order, as follows. As you can see, `canEmbedSQL` overrides all rules except the first (we obviously can't embed SQL if one of the dataSources isn't implemented using SQL)

    1. If either the "main" dataSource or the subquery dataSource is not an `sql dataSource`, the subquery must by separated. Otherwise,
    2. If the `canEmbedSQL` flag is non-null, we honor it. Otherwise,
    3. If the subquery specifies an explicit operationId, the subquery must be separated. Otherwise,
    4. If the subquery dataSource declares a custom fetch `operation` (that is, a fetch operation that does not declare an `operationId`), the subquery must be separated. Otherwise,
    5. If the subquery dataSource declares either of the dataSource-level customization properties `DataSource.serverObject` or `DataSource.script`, the subquery must be separated. Otherwise,
    6. If the subquery DataSource is a direct instance of the base class (ie, its canonical class name is `com.isomorphic.sql.SQLDataSource`), the subquery can be embedded. Otherwise,
    7. We now know that subquery dataSource is a subclass of `SQLDataSource`. We use Reflection to inspect the implementing class: if it overrides any of the methods `execute()`, `executeFetch{}` or `SQLExecute`, the subquery must be separated (note, we cache the results of these tests so Reflection is only used the first time a given dataSource instance is used)
    8. If we get to this point without having decided whether the subquery can be embedded, or must be separated, the subquery can be embedded

    Default value is null

    See Also:
      - `Advanced Filtering`

  - ### dataSource

    public String dataSource
    The name of the `DataSource` to use for this subquery

    Default value is null

    See Also:
      - `Advanced Filtering`

- ## Constructor Details

  - ### AdvancedCriterionSubquery

    public AdvancedCriterionSubquery()