Package com.smartgwt.client.data

Class AdvancedCriterionSubquery

java.lang.Object
com.smartgwt.client.core.JsObject
com.smartgwt.client.core.DataClass
com.smartgwt.client.core.RefDataClass
com.smartgwt.client.rpc.RPCRequest
com.smartgwt.client.data.DSRequest
com.smartgwt.client.data.AdvancedCriterionSubquery
All Implemented Interfaces:
HasHandlers
public class AdvancedCriterionSubquery extends DSRequest
A specialized subclass of DSRequest that you use to declare the properties of a subquery (a fieldQuery or valueQuery) to be used in AdvancedCriteria. 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 dataSources 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 DSRequests, 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 only make use of a single value as output from a subquery, 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 DSRequests 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

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")}
              }
          }
      ]
  });
See Also:

  • Constructor Details

    • AdvancedCriterionSubquery

      public AdvancedCriterionSubquery()

    • AdvancedCriterionSubquery

      public AdvancedCriterionSubquery(JavaScriptObject jsObj)

  • Method Details

    • getOrCreateRef

      public static AdvancedCriterionSubquery getOrCreateRef(JavaScriptObject jsObj)

    • setCanEmbedSQL

      public AdvancedCriterionSubquery setCanEmbedSQL(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
      Parameters:
      canEmbedSQL - New canEmbedSQL value. Default value is null
      Returns:
      AdvancedCriterionSubquery instance, for chaining setter calls
      See Also:

    • getCanEmbedSQL

      public Boolean getCanEmbedSQL()
      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
      Returns:
      Current canEmbedSQL value. Default value is null
      See Also:

    • setCriteria

      public AdvancedCriterionSubquery setCriteria(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"

      Overrides:
      setCriteria in class DSRequest
      Parameters:
      criteria - New criteria value. Default value is null
      Returns:
      AdvancedCriterionSubquery instance, for chaining setter calls
      See Also:

    • getCriteria

      public Criteria getCriteria()
      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"

      Overrides:
      getCriteria in class DSRequest
      Returns:
      Current criteria value. Default value is null
      See Also:

    • setDataSource

      public AdvancedCriterionSubquery setDataSource(String dataSource)
      The name of the DataSource to use for this subquery
      Overrides:
      setDataSource in class DSRequest
      Parameters:
      dataSource - New dataSource value. Default value is null
      Returns:
      AdvancedCriterionSubquery instance, for chaining setter calls
      See Also:

    • getDataSource

      public String getDataSource()
      The name of the DataSource to use for this subquery
      Overrides:
      getDataSource in class DSRequest
      Returns:
      Current dataSource value. Default value is null
      See Also:

    • setOperationId

      public AdvancedCriterionSubquery setOperationId(String operationId)
      Operation binding to use for this subquery. Note, this refers to an operationBinding on the subquery dataSource, not the DataSource associated with the overall fetch request. Also note that specifying an operationId will cause Smart GWT to run the subquery separately by default, which may have performance implications. This default behavior can be overridden - see canEmbedSQL for details.
      Overrides:
      setOperationId in class DSRequest
      Parameters:
      operationId - New operationId value. Default value is null
      Returns:
      AdvancedCriterionSubquery instance, for chaining setter calls
      See Also:

    • getOperationId

      public String getOperationId()
      Operation binding to use for this subquery. Note, this refers to an operationBinding on the subquery dataSource, not the DataSource associated with the overall fetch request. Also note that specifying an operationId will cause Smart GWT to run the subquery separately by default, which may have performance implications. This default behavior can be overridden - see canEmbedSQL for details.
      Overrides:
      getOperationId in class DSRequest
      Returns:
      Current operationId value. Default value is null
      See Also:

    • setQueryFK

      public AdvancedCriterionSubquery setQueryFK(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 Customers with a subquery on Orders, we discover Order.customerId FK pointing to Customer.id. More complicated relations are also auto-discovered: for example, querying Customers with a subquery on OrderLines, 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

      Parameters:
      queryFK - New queryFK value. Default value is null
      Returns:
      AdvancedCriterionSubquery instance, for chaining setter calls
      See Also:

    • getQueryFK

      public String getQueryFK()
      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 Customers with a subquery on Orders, we discover Order.customerId FK pointing to Customer.id. More complicated relations are also auto-discovered: for example, querying Customers with a subquery on OrderLines, 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

      Returns:
      Current queryFK value. Default value is null
      See Also:

    • setQueryOutput

      public AdvancedCriterionSubquery setQueryOutput(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
      Parameters:
      queryOutput - New queryOutput value. Default value is null
      Returns:
      AdvancedCriterionSubquery instance, for chaining setter calls
      See Also:

    • getQueryOutput

      public String getQueryOutput()
      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
      Returns:
      Current queryOutput value. Default value is null
      See Also: