Sorting a result set

You can use methods of the QueryFieldDef object to customize the sort order or of a result set as the following example illustrates. It sets the sort precedence on the id field and the sort order is ascending.

Perl

use CQPerlExt;

#Start a HCL Compass session

#$AdminSession= CQAdminSession::Build();

$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 not in closed AND (id = 1 OR id = 2))

$where = $querydef->BuildFilterOperator($CQPerlExt::CQ_BOOL_OP_AND);

@states = ("closed");

$where->BuildFilter("state", $CQPerlExt::CQ_COMP_OP_NEQ, \@states);

$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);



# Get the collection QueryFieldDef objects

$queryfielddefs = $querydef->GetQueryFieldDefs();

# Select the id field and set the sort type and order
# For VBScript, use the Item method, which takes a numeric index or a name

$idfield = $queryfielddefs->ItemByName("id");

$idfield->SetSortType($CQPerlExt::CQ_SORT_DESC);

# this is for if you have multiple sort columns, which takes precedence:

$idfield->SetSortOrder(1);

# Select the submit_date field and set the week function on it

$datefield = $queryfielddefs->ItemByName("submit_date");

$datefield->SetFunction($CQPerlExt::CQ_DB_WEEK_FUNC);



$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";

}

CQAdminSession::Unbuild($AdminSession);

CQSession::Unbuild($SessionObj);

            

VBScript

Set MyQuery = GetSession.BuildQuery("Users")
MyQuery.BuildField("login_name") ' Column 1
MyQuery.BuildField("fullname") ' Column 2
set MyField = MyQuery.QueryFieldDefs.Item("login_name")
MyField.SortType AD_SORT_ASC
MyField.SortOrder 1
set FilterOp = MyQuery.BuildFilterOperator(AD_BOOL_OP_AND)
FilterOp.BuildFilter "Groups.Name", AD_COMP_OP_EQ, "Testers"
set MyResults = GetSession.BuildResultSet(MyQuery)
MyResults.Execute
While MyResults.MoveNext = AD_SUCCESS
    Line = Line & MyResults.GetColumnValue(1) & " : " & _
                  MyResults.GetColumnValue(2) & VBCrlf
wend