< Previous | Next >

Defining your search criteria

You define a query's search criteria with query filters that are based on comparisons to the fields of each record in a user database. The query returns each record in the database that matches your search criteria in a ResultSet object.

Each comparison is implemented by a filter, which is an instance of the QueryFilterNode object. A filter allows you to compare a field to a single value or to a range of values. The operator you choose for the filter determines the type of comparison to perform. For a list of valid operators, see the CompOp constants enumerated type. To create a hierarchical tree of filters, join them together with a Boolean operator and nest some filters within other filters. Each filter consists of either a single condition or a group of conditions joined together with an AND or an OR operator. As you build your filters, you can nest more complex groups of filters to create a complex set of search logic.

When designing a Query, here are some of the commonly used objects and their methods for working with Queries:
  • QueryDef object:
    • BuildQuery (record type) - for a new query for a given record type.
    • BuildField (to specify the fields to be displayed).
    • BuildFilterOperator method requires an Enumerated Constant - BoolOpConstant (And/Or).
  • Session object:
    • BuildResultSet method (placeholder of fetched data from query).
  • ResultSet object:
    • BuildResultSet method (placeholder of fetched data from query).
    • Execute method (run the query and fetch the resulting data).
    • MoveNext method (moves the cursor to the next record in the data set).
    • GetColumnLabel method (obtains field label text) - for example, "Headline", "id", "State"
    • GetColumnValue method (obtains field specific data) - for example, "SAMPL00000014", "This is my headline", "Submitted"
  • QueryFilterNode object:
    • BuildFilter (filter parameters) requires a comparison Operator (such as In, Equal, Less Than, Greater Than, Is Null) that is a CompOpConstant Enumerated Constant (such as EQ, NEQ, GT, GTE, LT, LTE) and values to compare with (for example, State EQ Submitted), plus a Perl string array containing the values to compare with the values in the specified field.
    • BuildFilterOperator method requires an Enumerated Constant - BoolOpConstant

When you design a query, you can use the methods listed above as follows:

  1. Use the BuildQueryDef method to build a QueryDef object: $QueryDef = $Session->BuildQueryDef(entity-def-name)
  2. Add fields to be selected with the BuildField method: $QueryDef->BuildField(field-name)
  3. Build query filters as an And/Or tree. And/Or nodes are QueryFilterNode objects. Use the BuildFilterOperator method to construct the top node of the tree: $QueryFilterNode = $QueryDef->BuildFilterOperator(bool)
  4. Add And/Or comparison nodes as children of a QueryFilterNode object with these methods: QueryFilterNode = $QueryFilterNode->BuildFilter(field,CompOp,value) and $QueryFilterNode->BuildFilterOperator(bool) For example, to filter for priority = 1 and (component = gui or owner = joe): 
    $and_node = $querydef->BuildFilterOperator(And); 
$and_node->BuildFilter("priority",eq,1); 
$or_node = $and_node->BuildFilterOperator(Or); 
# The third argument of the BuildFilter method must be a reference to an array:
# Here we first build the array @comp, then pass 
# the reference \@comp into the BuildFilter method.
@comp = ("gui");
$or_node->BuildFilter("component", $CQPerlExt::CQ_COMP_OP_EQ, \@comp); 
@name = ("Joe"); 
$or_node->BuildFilter("owner", $CQPerlExt::CQ_COMP_OP_EQ, \@name);

# Another way to pass the array reference for simple values is using anonymous arrays. For example:
$or_node->BuildFilter("owner", $CQPerlExt::CQ_COMP_OP_EQ, ["Joe"]);

Example

This example extends the example in the previous topic by including some query filters: 

use CQPerlExt; 
#Start a Rational ClearQuest session 
$SessionObj = CQSession::Build(); 

$dbsetname = "CQMS.SAMPL.HOME"; 

#Refresh list of accessible databases 
$databases = $SessionObj->GetAccessibleDatabases("MASTR", "", $dbsetname); 

#Log into a database 
$SessionObj->UserLogon("admin","","SAMPL",$dbsetname); 

#Create a Query 
$querydef = $SessionObj->BuildQuery("defect"); 
$querydef->BuildField("id"); 
$querydef->BuildField("headline"); 
$querydef->BuildField("owner.login_name"); 
$querydef->BuildField("submit_date"); 

#Create the queryfilternode object: 
# where (state is not Closed AND (id = 1 OR id = 2)) 
$where = $querydef->BuildFilterOperator($CQPerlExt::CQ_BOOL_OP_AND); 

# All filter values passed in as a reference to an array.
@states = ("closed"); 
$where->BuildFilter("state", $CQPerlExt::CQ_COMP_OP_NEQ, \@states); 
# Another way to pass the array reference for simple values is using an anonymous array, like this:
# $where->BuildFilter("state", $CQPerlExt::CQ_COMP_OP_EQ, ["closed"]);

$subor = $where->BuildFilterOperator($CQPerlExt::CQ_BOOL_OP_OR); 

@id1 = ("SAMPL00000001"); 
$subor->BuildFilter("id", $CQPerlExt::CQ_COMP_OP_EQ, \@id1); 

@id2 = ("SAMPL00000002"); 
$subor->BuildFilter("id", $CQPerlExt::CQ_COMP_OP_EQ, \@id2); 

$resultset = $SessionObj->BuildResultSet($querydef); 

$ct = $resultset->ExecuteAndCountRecords(); 
for ($i = 0; $i < $ct; $i++) { 
   $resultset->MoveNext(); 
   print $resultset->GetColumnValue(1); 
   print " "; 
   print $resultset->GetColumnValue(2); 
   print " "; 
   print $resultset->GetColumnValue(3); 
   print " "; 
   print $resultset->GetColumnValue(4); 
   print "\n"; 
   } 
CQSession::Unbuild($SessionObj);
< Previous | Next >
Feedback