Embedded Objects - Criteria Elements

The criteriaElements of an embedded object will be used to build query criteria to retrieve an entity or entity list from persistence, or initialize entity properties for create or query view.

For example,


<criteriaElements>

	<function name="EQ" property="employee.type">FULL_TIME</function>
  
	<function name="EQ">
		<path>employee.type</path>  	
	  	<literal type="myPackage.Employee.Type">FULL_TIME</literal>
	</function>
  
  	<function name="IN">
		<path>employee.type</path>  	
  		<literal type="java.lang.List" memberType="myPackage.Employee.Type">FULL_TIME, PART_TIME</literal>
	</function>
	
</criteriaElements>
A criteria element is mapped to a CriteriaElement instance: expression, select, order, group, group restriction, and hint.

Expression

An expression can be a property path, literal value, function, predicate, switch case, alias or subquery, and it can be used in query selections, restrictions, order by and group by, and having group restriction.

Expressions can be a simple literal or compound with nested expressions. For example, list those ExpenseCliams with more than 5 items and for employees hired before year 2015.



<and>
	<function name="GT">
		<function name="SIZE" property="expenseItems" />
		<literal>5</literal>
	</function>
	
	<function name="LT">
		<function name="year" native="true" resultType="java.lang.Integer">
			<path>employee.hiredDate</path>
		</function>
		<literal>2015</literal>
	</function>
</and>

Function

A function is an expression with function name and a number of arguments(expressions). Supported function is defined in CriteriaFunction.Function.

  	<function name="IN">
		<path>employee.type</path>  	
  		<literal>FULL_TIME, PART_TIME</literal>
	</function>
	
	<function name="CONCAT">
		<path>employee.firstName</path>
		<literal>,</literal> 	
  		<path>employee.lastName</path>
	</function>
A function can be a native function supported by underlying database. For example, function year(date) returns the year of a date.

	<function name="year" native="true" resultType="java.lang.Integer">
		<path>employee.hiredDate</path>
	</function>

A function with property name and value can be written in 3 different ways.


	<function name="EQ" property="employee.type">FULL_TIME</function>

	<function name="EQ" property="employee.type">
		<literal>FULL_TIME</literal>
	</function>

	<function name="EQ">
		<path>employee.type</path>
		<literal>FULL_TIME</literal>
	</function>
EQ(equal) functions with property name and value will be used to initialize entity property values if applyToCreate is true(default) for create view or if hiddenForQuery is false (default) for query view.

	<function name="EQ" applyToCreate="true" applyImmediate="false" hiddenForQuery="true">
		<path>employee.type</path>  	
	  	<literal type="myPackage.Employee.Type">FULL_TIME</literal>
	</function>

Literal

For a literal expression, its type and memberType(for collection) can be specified. If they are not specified, reflection will be used to determined their types. For example, a simple value or a collection:

	<literal type="java.lang.Integer">123</literal>
			
	<literal type="java.util.List" memberType="myPackage.ExpenseClaimItem">
		<expenseClaimItem>
			<code>00</code>
			<expense>200.00</expense>
		</expenseClaimItem>

		<expenseClaimItem>
			<code>01</code>
			<expense>300.00</expense>
		</expenseClaimItem>
	</literal>
	
	<literal type="java.util.Map" 
		mapEntryKeyType="java.lang.String" mapEntryValueType="java.lang.Integer">
		<entry>
			<key>001</key>
			<value>200</value>
		</entry>

		<entry>
			<key>002</key>
			<value>300</value>
		</entry>
	</literal>	

Literal values support the following expressions in addition to JSF EL expressions:

#{current_user}, #{current_user.propertyName}, #{current_date}, #{current_date.calendarFieldName}

Literal values will be converted to their associated property types. For example,

  <function name="EQ" property="creator">#{current_user}</function>
  <function name="EQ" property="author">#{current_user.name}</function>
  <function name="EQ" property="createdDate">#{current_date}</function>
  <function name="EQ" property="year">#{current_date.YEAR}</function>
  <function name="EQ" property="type">#{param.employeeType}</function>
  <function name="IN" property="type">FULL_TIME,PART_TIME</function>  
  <function name="EQ" property="bookmarkableURL">#{containerBean.viewInfo.bookmarkableURL}</function>

Predicate

The default relationship for a list of expressions is conjunction(and). Conjunction(and), disjunction(or) and negate(not) can be combined to create a predicate. For example,

<criteriaElements>

	<or>
		<function name="NE" property="type">FULL_TIME</function>
		<function name="GE" property="hiredDate">2010-5-18</function>
	</or>
  
	<not>
		<function name="EQ" property="type">FULL_TIME</function>
	</not>
  
	<or>
		<and>
			<function name="EQ" property="type">FULL_TIME</function>
			<function name="GE" property="hiredDate">2010-5-18</function>
		</and>
    
		<not>
			<function name="EQ" property="type">FULL_TIME</function>
		</not>
	</or>
	
</criteriaElements>  

Switch Cases

An expression with switch cases returns different values under different conditions. A switch case has a condition expression and a value expression. If a switch case has one expression only, it is the else case and the expression is its value expression.

For example, return 5 if expense is greater than $5000; return 2 if expense is greater than $2000 but less than or equal to $5000; return 0 otherwise.


	<switchCases>
		<case>
			<function name="GT" property="expense">5000</function>
	
			<literal>5</literal>
		</case>
		
		<case>
			<and>
				<function name="GT" property="expense">2000</function>
				<function name="LE" property="expense">5000</function>
			<and>
			
			<literal>2</literal>
		</case>
				
		<case>
			<literal>0</literal>
		</case>
	</switchCases>

Subquery

Subquery is an expression, and can be collated with its parent query. For example, list expenseClaims with a least two items whose expenses are greater than $1000.

<object xmlns="http://www.cmobilecom.com/af/objects"
        id="expenseClaims" type="entities">
	<entityType>ExampleHR.EC</entityType>
  
	<criteriaElements>
	
		<function name="GE">
			<subquery resultType="java.util.Integer" collate="" />
				<select>
					<function name="COUNT" property="expenseClaimItems.id" />
				</select>
				
				<function name="GT" property="expenseClaimItems.expense">1000</function>
			</subquery>
			
			<literal>2</literal>
		</function>
		
	</criteriaElements>

</object>	
If the collate property is empty string, it stands for the root of parent query. The subquery collates to the root of parent query ExpenseClaim as its root.

A subquery can be independent from its parent query. For example, select total expense amount and total number of distinct expense item codes.


<criteriaElements>

	<select>
	
		<function name="SUM" property="expense" />
	
		<subquery resultType="java.util.Integer" entityType="myPackage.ExpenseClaimItem" />
			<select>
				<function name="COUNT_DISCOUNT" property="code" />
			</select>
		</subquery>
		
	</select>
	
</criteriaElements>

Join Graph

For example, retrieve an orderItem inner join orderItem.order inner join orderItem.order.creator left join orderItem.order.creator.roles.

	<joinGraph>
		<property name="order" joinType="INNER">
			<property name="creator" joinType="INNER">
					<properties joinType="LEFT">roles</properties>
			</property>
		</property>
	</joinGraph>

Fetch Graph

For example, retrieve orders

	<fetchGraph exclusive="false">
		<properties>description</properties>
		<properties joinType="INNER">customer</properties>
		<properties joinType="LEFT">orderItems,shipTo,billTo</properties>
		<property name="payment" joinType="LEFT">
			<properties joinType="INNER">paymentMethod</properties>
		</property>
	</fetchGraph>

Select

Select a number of expressions. For example,

<select>
	<path>employee.type</path>
	
	<function name="SUM" property="expense" />
	
	<function name="SUM">
		<function name="SIZE" property="expenseItems" />
	</function>
</select>

Alias

A selection can be assigned an alias that can be referenced in order, group and having restriction. For example,

<select>
	<path alias="empType">employee.type</path>
	
	<function name="SUM" property="expense" alias="totalAmount" />
	
	<function name="SUM" alias="totalItems">
		<function name="SIZE" property="expenseItems" />
	</function>
</select>

Order

Order ascending or descending by an expression where aliases can be used. For example,

<asc property="employee.type" />
	
<asc>
	<function name="year" native="true" resultType="java.lang.Integer">
		<path>employee.hiredDate</path>
	</function>
</asc>

<desc>
	<alias>totalAmount</alias>
</desc>

<desc>
	<function name="CONCAT">
		<path>employee.type</path>
		<alias>totalItems</alias>
	</function>
</desc>

Group

Group by a number of expressions where aliases can be used. For example,
	
<group>
	<alias>empType</alias>
	
	<function name="year" native="true" resultType="java.lang.Integer">
		<path>employee.hiredDate</path>
	</function>
</group>
To group and select a number of expressions, set attribute select to true. For example,
	
<group select="true">
	<path alias="employeeType">employee.type</path>
	
	<function name="year" native="true" resultType="java.lang.Integer"
		alias="hiredYear">
		<path>employee.hiredDate</path>
	</function>
</group>
The specified aliases will be assigned to selections and used in grouping.

Having

Having(group restriction) specifies a number of expressions that will be AND-ed(conjunction) to return a predicate. Aliases can be used in group restriction. For example,
	
<having>
	<function name="EQ" property="employee.type">
		<literal>FULL_TIME</literal>
	</function>
		
	<function name="GE"
		<alias>hiredYear<alias>
		<literal>2015</literal>
	</function>
</having>

Examples

Example: set employee type to FULL_TIME(will be converted to enum type) for query or creating entity.
<object xmlns="http://www.cmobilecom.com/af/objects"
        id="object1" type="entity">
        
	<entityType>ExampleHR.EMP</entityType>
  
	<criteriaElements>
		<function name="EQ" property="type">FULL_TIME</function>
	</criteriaElements>
  
	...
</object>
Example: create an Expense Claim item collection

<object xmlns="http://www.cmobilecom.com/af/objects"
        id="createExpenseClaim" type="entity">
	<entityType>ExampleHR.EC</entityType>
	<mode>CREATE</mode>
  
	<criteriaElements>
		<function name="EQ" property="expenseClaimItems">
			<literal>
				<expenseClaimItem>
					<code>00</code>
					<expense>200.00</expense>
				</expenseClaimItem>

				<expenseClaimItem>
					<code>01</code>
					<expense>300.00</expense>
				</expenseClaimItem>
			</literal>
		</function>
	</criteriaElements>

</object>
Example: list all unpaid orders. Left join payment property to retrieve orders without payment.

<object xmlns="http://www.cmobilecom.com/af/objects"
        id="unpaidOrders" type="entities">
	<entityType>Ecommerce.SO</entityType>
  
	<criteriaElements>
  
		<function name="IS_NULL" property="payment"></function>
    
		<joinGraph>
			<properties joinType="LEFT">payment</properties>
		</joinGraph>
   
	</criteriaElements>

</object>