Page Comparison

Performs relational database queries on domain database.

...

Relational database queries uses special markup to specify query to execute. Query structure is similar to SQL Select command but you may use sub queries and join clauses automatically handled.

Query Structure

<Query<Query

Start="0"
Start index of results to fetch. If not specified 0 value is used.

MaxLength="-1">
Maximum number of results to fetch. If not specified -1 (maximum results) is used.

<Parameters>
Query parameters.

<TargetSchema>MySchema</TargetSchema>
Name of schema of query target. Required.

<TargetTable>MyTable</TargetTable>
Name of table of query target. Required.

</Parameters>

<Columns>
List of columns to include in results.

<Column
Column The column definition. Any number of column can be specified.

Name="MyColumn"
Name of column or relation path. Please see Column Name for more information. Required.

Formula="None">
Aggregation formula like count, max etc. If not specified no aggregation is applied. Please see Aggregation Formula for more information.

<Properties>
Column specific properties. Currently

...

<Column></Column>

</Columns>

<Where

Condition="And">

...

>
Query criteria and block list. Criteria can be consist of criteria definitions or block definitions to group criteria for logical conditions (and / or).

<Criteria>
List of criteria values.

<Criteria
The criteria definition. Any number of criteria can be specified.

Name="MyColumn"
Column name or relation path to criteria to apply. Required.

Comparison="Equals"
Comparison method of criteria like equals or greater etc. If not specified Equals value is used. Please see Comparison for more information.

Condition="And"
Condition of criteria to merge with previous criteria in list. "And", "Or" values are valid. If not used "And" value is used.

Formula="None">

<IgnoreIfEquals>MyValue</IgnoreIfEquals>

</Properties>

</Criteria>

</Criteria>

...

Aggregation formula like count, max etc. If not specified no aggregation is applied. Please see Formula for more information.

<Value></Value>
Value of criteria. Value can be specified in static value or template format like "{{Code}}" or "000-{{Code}}-1111" to dynamically evaluate.
If not specified value used as empty string.
You can use "NULL" string value to specify null value.

<Properties>
Criteria specific custom properties. Currently IgnoreIfEquals only used.

<IgnoreIfEquals></IgnoreIfEquals>
Specify to ignore this criteria if evaluated value is equals to value. In some cases you may want to remove criteria for specific values. For example; if user leaves filter value empty you may want to list all rows, not equal to '' (empty string)

</Properties>

</Criteria>

<Criteria> ............ </

...

Criteria>

</

...

Criteria>

...

<

...

</Blocks>

</Where>

</Query>

</SubQueries>

</Query>

This control allows you to query your emakin based relational databases and map them with your form data.

Here are samples;

Simple sample 1:

Simple sample 2:

Advance sample:

</Parameters>

</Properties>

</Column>

</Properties>

</Column>

</Properties>

</Column>

</Properties>

</Column>

</Properties>

</Column>

</Properties>

</Column>

</Properties>

</Column>

</Properties>

</Column>

</Properties>

</Column>

</Columns>

</Criteria>

</Criteria>

</Block>

</Criteria>

</Properties>

</Criteria>

</Properties>

</Criteria>

</Properties>

</Criteria>

</Properties>

</Criteria>

</Properties>

</Criteria>

</Properties>

</Criteria>

</Properties>

</Criteria>

</Properties>

</Criteria>

</Properties>

</Criteria>

</Properties>

</Criteria>

</Block>

</Blocks>

</Where>

<Order>

Blocks>
List of blocks to group criteria.

<Block
The block definition.

Condition="And">
Condition to merge with previous blocks. If not specified And value is used.

<Criteria>
List of criteria. Same with above criteria definition.

<Criteria> ............ </Criteria>

</Criteria>

</Block>

</Blocks>

</Where>

<Order>
List of ordering columns.

<Order
The order definition.

Name="MyColumn"
Column name or relation path to apply order. Required.

Type="Ascending"/>
Type of ordering. "Ascending", "Descending" values are valid. If not specified "Ascending" value is used.

</Order>

<SubQueries>
List of subqueries to return as related child rows.

<Query
Subquery definition. Same with above only Name attribute is required.

Name="">
Name of table relation to get rows as child.

</Query>

</SubQueries>

</Query>

Formula

Formula specifies the method of evaluation of row values. Formula enumeration can be one of following values;

Value	Description
None	No aggregation is applied. All values in rows.
Count	Count of row values.
CountDistinct	Count of distinct values in rows.
Sum	Sum of row values. Valid for numeric type columns.
SumDistinct	Sum of distinct values in rows. Valid for numeric type columns.
Avg	Average of row values. Valid for numeric type columns.
AvgDistinct	Average of distinct values in rows. Valid for numeric type columns.
Min	Minimum value in rows.
Max	Maximum value in row values.

Comparison

Specifies the comparison method of criteria value.

Value	Description
Equals	All rows with same value.
LessThan	All rows that less than criteria value.
LessThanOrEqualTo	All rows that less or equals to criteria value.
GreaterThan	All rows that greater than criteria value.
GreaterThanOrEqualTo	All rows that greater or equals to criteria value.
Different	All rows with different than criteria value.
Like	All rows that likes to criteria value. Criteria value can be used with * (star character) Like ABC, ABC, ABC

Column Name

Column names can be specified as directly column name or relation path. Relation paths can be specified as "Relation.Relation.Column" format.

Image Added

For example you can use following values as column name.

Column Name	Description
Name	Name of customer
Orders.Id	Id of order
Orders.Product.Name	Name of ordered product

Example Query

Following query returns the all customer name and count of orders by product name criteria. Each row also contains the list of related customer orders as a child rows.

Code Block

<Query>
	<Parameters>
    	<TargetSchema>Customer</TargetSchema>
		<TargetTable>MySchema</TargetTable>
	</Parameters>
	<Columns>
		<Column Name="Name" Formula="None" />
		<Column Name="Orders.Id" Formula="Count" />
	</Columns>
	<Where>
		<Criteria>
			<Criteria Name="Orders.Product.Name" Comparison="Like">
				<Value>{{ProductName}}</Value>
			</Criteria>
		</Criteria>
	</Where>
    <Order>
		<Order Name="Orders.Product.Name" Type="Ascending"/>  
	</Order>
	<SubQueries>
		<Query Name="Orders">
			<Columns>
				<Column Name="Id" />
				<Column Name="ProductId" />
			</Columns>
		    <Order>
				<Order Name="Product.Name" Type="Ascending"/>  
			</Order>
		</Query>
	</SubQueries>
</Query>

Versions Compared

Old Version 10

New Version 11

Key

Query Structure

Formula

Comparison

Column Name

Example Query