public class AdvancedCriterionSubquery extends DSRequest
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:
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, etcinSet-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.
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:
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 failqueryFK
- 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
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
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
}
]
});
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'}
]
}
}
}
]
});
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")}
}
}
]
});
Advanced FilteringidfactoryCreated, factoryProperties| Constructor and Description |
|---|
AdvancedCriterionSubquery() |
AdvancedCriterionSubquery(com.google.gwt.core.client.JavaScriptObject jsObj) |
| Modifier and Type | Method and Description |
|---|---|
java.lang.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.
|
Criteria |
getCriteria()
Optional criteria to use for this subquery.
|
java.lang.String |
getDataSource()
The name of the
DataSource to use for this subquery |
java.lang.String |
getOperationId()
Operation binding to use for this subquery. |
static AdvancedCriterionSubquery |
getOrCreateRef(com.google.gwt.core.client.JavaScriptObject jsObj) |
java.lang.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. |
java.lang.String |
getQueryOutput()
The name of the field that will be used as the output of this query.
|
AdvancedCriterionSubquery |
setCanEmbedSQL(java.lang.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.
|
AdvancedCriterionSubquery |
setCriteria(Criteria criteria)
Optional criteria to use for this subquery.
|
AdvancedCriterionSubquery |
setDataSource(java.lang.String dataSource)
The name of the
DataSource to use for this subquery |
AdvancedCriterionSubquery |
setOperationId(java.lang.String operationId)
Operation binding to use for this subquery. |
AdvancedCriterionSubquery |
setQueryFK(java.lang.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. |
AdvancedCriterionSubquery |
setQueryOutput(java.lang.String queryOutput)
The name of the field that will be used as the output of this query.
|
getAdditionalOutputs, getArrayCriteriaForceExact, getComponentId, getDataProtocol, getEndRow, getExportAs, getExportCSS, getExportData, getExportDatesAsFormattedString, getExportDelimiter, getExportDisplay, getExportFields, getExportFilename, getExportFooter, getExportHeader, getExportHeaderless, getExportImageFormat, getExportImageQuality, getExportImageQualityAsDouble, getExportNumbersAsFormattedString, getExportPath, getExportPropertyIdentifier, getExportRawValues, getExportResults, getExportShowHeaderSpanTitles, getExportSpanTitleSeparator, getExportStreaming, getExportTitleSeparatorChar, getExportToClient, getExportToFilesystem, getExportTZ, getExportValueFields, getFieldValueExpressions, getGenerateRelatedUpdates, getGroupBy, getHeaderData, getKeepParentsOnFilter, getLineBreakStyle, getLinkDataFetchOperation, getOldValues, getOperationType, getOutputs, getParentNode, getPendingAdd, getProgressiveLoading, getRawSummaryFunctions, getRequestId, getResultSet, getResultTree, getShouldUseCache, getSortBy, getStartRow, getStreamResults, getSummaryFunctions, getTextMatchStyle, getUseFlatFields, getUseFlatHeaderFields, getUseStrictJSON, getValidationMode, setAdditionalOutputs, setArrayCriteriaForceExact, setAttribute, setCallback, setComponentId, setDataProtocol, setEndRow, setExportAs, setExportCSS, setExportData, setExportDatesAsFormattedString, setExportDelimiter, setExportDisplay, setExportFields, setExportFilename, setExportFooter, setExportHeader, setExportHeaderless, setExportImageFormat, setExportImageQuality, setExportImageQuality, setExportNumbersAsFormattedString, setExportPath, setExportPropertyIdentifier, setExportRawValues, setExportResults, setExportShowHeaderSpanTitles, setExportSpanTitleSeparator, setExportStreaming, setExportTitleSeparatorChar, setExportToClient, setExportToFilesystem, setExportTZ, setExportValueFields, setFieldValueExpressions, setGenerateRelatedUpdates, setGroupBy, setHeaderData, setKeepParentsOnFilter, setLineBreakStyle, setLinkDataFetchOperation, setOldValues, setOldValues, setOldValues, setOperationType, setOutputs, setParams, setPendingAdd, setProgressiveLoading, setRawSummaryFunctions, setShouldUseCache, setSkinName, setSortBy, setSortBy, setStartRow, setStreamResults, setSummaryFunctions, setTextMatchStyle, setUseFlatFields, setUseFlatHeaderFields, setUseStrictJSON, setValidationModedownloadCallback, getActionURL, getAllowIE9Leak, getBypassCache, getCallbackParam, getContainsCredentials, getContentType, getData, getDataAsString, getDownloadResult, getDownloadToNewWindow, getEvalResult, getHttpHeaders, getHttpMethod, getHttpProxyURL, getIgnoreTimeout, getIsBackgroundRequest, getMockMode, getOmitNullMapValuesInResponse, getParams, getPrompt, getPromptCursor, getPromptDelay, getPromptStyle, getReportDownloadErrorsAsDocuments, getSendNoQueue, getServerOutputAsString, getShowPrompt, getSuppressAutoDraw, getTimeout, getTransport, getUseCursorTracker, getUseHttpProxy, getUseSimpleHttp, getWillHandleError, getWithCredentials, isDSRequest, setActionURL, setAllowIE9Leak, setBypassCache, setCallbackParam, setContainsCredentials, setContentType, setData, setData, setData, setData, setDownloadResult, setDownloadToNewWindow, setEvalResult, setEvalVars, setHttpHeaders, setHttpMethod, setHttpProxyURL, setIgnoreTimeout, setIsBackgroundRequest, setMockMode, setOmitNullMapValuesInResponse, setPrompt, setPromptCursor, setPromptDelay, setPromptStyle, setReportDownloadErrorsAsDocuments, setSendNoQueue, setServerOutputAsString, setShowPrompt, setSuppressAutoDraw, setTimeout, setTransport, setUseCursorTracker, setUseHttpProxy, setUseSimpleHttp, setWillHandleError, setWithCredentials, xhr_onProgressgetRef, getRef, internalSetIDapplyFactoryProperties, doAddHandler, fireEvent, getAttribute, getAttributeAsBoolean, getAttributeAsBoolean, getAttributeAsDate, getAttributeAsDouble, getAttributeAsDoubleArray, getAttributeAsElement, getAttributeAsFloat, getAttributeAsInt, getAttributeAsIntArray, getAttributeAsJavaScriptObject, getAttributeAsLong, getAttributeAsMap, getAttributeAsObject, getAttributeAsRecord, getAttributeAsString, getAttributeAsStringArray, getAttributes, getHandlerCount, isFactoryCreated, setAttribute, setAttribute, setAttribute, setAttribute, setAttribute, setAttribute, setAttribute, setAttribute, setAttribute, setAttribute, setAttribute, setAttribute, setAttribute, setAttribute, setAttribute, setAttribute, setAttribute, setAttribute, setAttribute, setAttribute, setAttribute, setAttribute, setAttribute, setAttributeAsJavaObject, setFactoryCreatedpublic AdvancedCriterionSubquery()
public AdvancedCriterionSubquery(com.google.gwt.core.client.JavaScriptObject jsObj)
public static AdvancedCriterionSubquery getOrCreateRef(com.google.gwt.core.client.JavaScriptObject jsObj)
public AdvancedCriterionSubquery setCanEmbedSQL(java.lang.Boolean canEmbedSQL)
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)
sql dataSource, the subquery must by separated.
Otherwise,canEmbedSQL flag is non-null, we honor it. Otherwise,operation (that is, a fetch operation that
does not declare an operationId), the subquery must be
separated. Otherwise,DataSource.serverObject or DataSource.script, the subquery must be separated. Otherwise,com.isomorphic.sql.SQLDataSource), the subquery can be embedded. Otherwise,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)canEmbedSQL - New canEmbedSQL value. Default value is nullAdvancedCriterionSubquery instance, for chaining setter callsAdvanced Filteringpublic java.lang.Boolean getCanEmbedSQL()
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)
sql dataSource, the subquery must by separated.
Otherwise,canEmbedSQL flag is non-null, we honor it. Otherwise,operation (that is, a fetch operation that
does not declare an operationId), the subquery must be
separated. Otherwise,DataSource.serverObject or DataSource.script, the subquery must be separated. Otherwise,com.isomorphic.sql.SQLDataSource), the subquery can be embedded. Otherwise,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)Advanced Filteringpublic AdvancedCriterionSubquery setCriteria(Criteria criteria)
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"
setCriteria in class DSRequestcriteria - New criteria value. Default value is nullAdvancedCriterionSubquery instance, for chaining setter callsAdvanced Filteringpublic Criteria getCriteria()
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"
getCriteria in class DSRequestAdvanced Filteringpublic AdvancedCriterionSubquery setDataSource(java.lang.String dataSource)
DataSource to use for this subquerysetDataSource in class DSRequestdataSource - New dataSource value. Default value is nullAdvancedCriterionSubquery instance, for chaining setter callsAdvanced Filteringpublic java.lang.String getDataSource()
DataSource to use for this subquerygetDataSource in class DSRequestAdvanced Filteringpublic AdvancedCriterionSubquery setOperationId(java.lang.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.setOperationId in class DSRequestoperationId - New operationId value. Default value is nullAdvancedCriterionSubquery instance, for chaining setter callsOperations Overviewpublic java.lang.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.getOperationId in class DSRequestOperations Overviewpublic AdvancedCriterionSubquery setQueryFK(java.lang.String queryFK)
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
queryFK - New queryFK value. Default value is nullAdvancedCriterionSubquery instance, for chaining setter callsAdvanced Filteringpublic java.lang.String getQueryFK()
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
Advanced Filteringpublic AdvancedCriterionSubquery setQueryOutput(java.lang.String queryOutput)
Subqueries
overview for more detailsqueryOutput - New queryOutput value. Default value is nullAdvancedCriterionSubquery instance, for chaining setter callsAdvanced Filteringpublic java.lang.String getQueryOutput()
Subqueries
overview for more detailsAdvanced Filtering