3.6.2.2.3. Query Filter
This is a legacy API. For new data API available since release 7.0, see Data Components. |
A datasource query can be modified at runtime depending on conditions entered by the user. This allows you to efficiently filter data at the database level.
The easiest way to provide such ability is to connect the datasource to a special visual component: Filter.
If the universal filter is not suitable for some reason, a special XML markup can be embedded into the query text. This allows you to create a resulting query based on values entered by the user into any visual components of the screen.
The following elements can be used in this filter:
-
filter
– a root element of the filter. It can directly contain only one condition.-
and
,or
– logical conditions, may contain any number of other conditions and statements. -
c
– JPQL condition, which is added to thewhere
section of the query. If the query does not containwhere
clause, it will be added before the first condition. An optionaljoin
attribute can be used to specify joined entities. The value of thejoin
attribute is added after the root entity declaration as is, so it must containjoin
keyword or comma.
-
Conditions and statements are added to the resulting query only if corresponding parameters have values, i.e. when they are not null
.
Example:
<query>
<![CDATA[select distinct d from app$GeneralDoc d]]>
<filter>
<or>
<and>
<c join=", app$DocRole dr">dr.doc.id = d.id and d.processState = :custom$state</c>
<c>d.barCode like :component$barCodeFilterField</c>
</and>
<c join=", app$DocRole dr">dr.doc.id = d.id and dr.user.id = :custom$initiator</c>
</or>
</filter>
</query>
In this case, if state
and initiator
parameters are passed to the refresh()
method of the datasource, and the barCodeFilterField
visual component has some value, then the resulting query will be as follows:
select distinct d from app$GeneralDoc d, app$DocRole dr
where
(
(dr.doc.id = d.id and d.processState = :custom$state)
and
(d.barCode like :component$barCodeFilterField)
)
or
(dr.doc.id = d.id and dr.user.id = :custom$initiator)
If, for example, the barCodeFilterField
component is empty and only initiator
parameter is passed to the refresh()
method, the query will be as follows:
select distinct d from app$GeneralDoc d, app$DocRole dr
where
(dr.doc.id = d.id and dr.user.id = :custom$initiator)