Finding Case Objects by Criteria

From within a script, use the findByCriteria method on the appropriate case access class to return a list of case references that match the criteria defined in a query string.

The query must be expressed in Data Query Language (DQL), and can be supplied either as a string or in a previously created criteria object.

Method syntax Description
findByCriteria(criteriaObject) Returns a paginated list of case references that match the criteria specified in the criteriaObject.
findByCriteria(DQLString) Returns a list of case references that match the criteria specified in the DQLString.
findByCriteria(DQLString,index,pageSize) Returns a paginated list of case references that match the criteria specified in the DQLString.

where:

  • DQLString defines the query to be executed. See Case Data Query Language (DQL).
  • criteriaObject is a previously created criteria object.
  • index is the (zero-based) number of the first record to return in a paginated list.
  • pageSize is the number of records to return in each page of a paginated list. A value of -1 means return all records.

Examples

This example shows three different ways of using a query string. 

	// Find customers who have no orders (returning references to the first 100 results).
	var custRefs = cac_com_example_ordermodel_Customer.findByCriteria("size(orders) = 0", 0, 100);

	// Find orders from customers whose name begins with “EasyAs” (returning references to the first 20).
	var ordRefs = cac_com_example_ordermodel_Order.findByCriteria("customer.name='EasyAs*'",0,20);

	// Find customers who have orders that include Part Number PN:10001 
	// ordering the results by customer name (returning up to 100 customer references).
	var custRefs = cac_com_example_ordermodel_Customer.findByCriteria("orders.orderLines.orderItem.partNum = 'PN:10001' order by name", 0, 100);

This example shows how to use a query object, and how to use parameters to re-initialize the query on different executions. 

	// Create a new Criteria object defining a query to run against the Order class.
	var criteria = cac_com_example_ordermodel_Order.createCriteria("orderTakers.name = :orderClerk ORDER BY orderTaker.name ASC", 0, 10);

	// Set the initial value of the orderClerk parameter.
	criteria.setQueryParameter("orderClerk", "Fred%");

	// Execute the query.
	var orderList1 = cac_com_example_ordermodel_Order.findByCriteria(criteria);
	var ref1  = orderList1.get(0);
	var order1= ref1.readOrder();
	
	// Run the query again using a different orderClerk parameter value.
	criteria.setQueryParameter("orderClerk", "Ginger%");
	var orderList2 = cac_com_example_ordermodel_Order.findByCriteria(criteria);
	var ref2  = orderList2.get(0);
	var order2= ref2.readOrder();

This example shows the use of a parameter with multiple values to find customers whose name begins with Fred, Ginger or Eric. 

// Create a list of user patterns that we want to find.
	var userList = Datautil.createList();
	userList.add("Fred%");
	userList.add("Ginger%");
	userList.add("Eric%");

	// Create a new Criteria object to run against the Customer class.
	var criteria=cac_com_example_ordermodel_Customer.createCriteria("name IN :userList",0,100);
	criteria.setQueryParameter("userList", userList);

	// Execute the query.
	var customerList = cac_com_example_ordermodel_Customer.findByCriteria(criteria);
	var ref1 = customerList.get(0);
	var customer = ref1.readCustomer();

The final example demonstrates two ways of finding customers whose orders were dispatched in August 2013:

  • using a query string, 
    var customerList = cac_com_example_ordermodel_Customer.findByCriteria("orders.dateOfDispatch between 2013-08-01 and 2013-08-31");
  • or using a query object with parameters. 
    var startDate = DateTimeUtil.createDate("2013-08-01");
	var endDate = DateTimeUtil.createDate("2013-08-31");

	var dateRange = DataUtil.createList();
	dateRange.add(startDate);
	dateRange.add(endDate);

	var criteria = cac_com_example_ordermodel_Customer.createCriteria("orders.dispatchNote.dateOfDispatch between :dateRng");
	criteria.setQueryParameter("dateRng", dateRange);

	var customerList = cac_com_example_ordermodel_Customer.findByCriteria(criteria);