Building queries for defects and users

The following code fragments show how to build queries that fetch records from the database by using criteria about defects and users. The samples use the QueryDef and QueryFilterNode objects, as well as a Structured Query Language (SQL) query.

Note: You can use any of the following code fragments in a hook such as a field choice list hook or a field validation hook. However, you can also include this code in an external application if you manually create the session object and log on to the database (instead of getting the session object).

VBScript

The following example selects all defects that belong to the defect record type.

set session = GetSession

set querydef = session.BuildQuery("defect")
querydef.BuildField("id")
querydef.BuildField("headline")


set resultset = session.BuildResultSet(querydef)

VBScript

The following example selects defects that match these criteria:

"assigned to" user "johndoe"
"beta2" planned release

This translates to:

(assigned_to = "johndoe") AND (planned_release = "beta2")

set session = GetSession

set querydef = session.BuildQuery("defect") 
querydef.BuildField("id") 
querydef.BuildField("headline") 


set operator = querydef.BuildFilterOperator(AD_BOOL_OP_AND)
operator.BuildFilter "assigned_to", AD_COMP_OP_EQ, "johndoe" 
operator.BuildFilter "planned_release", AD_COMP_OP_EQ, "beta2"


set resultset = session.BuildResultSet(querydef)

VBScript

The following example selects defects that match these criteria:

Planned release of beta
Not in resolved or verified states
Priority levels 1 or 2
Assigned to a certain set of users

This translates to:

((planned_release = "beta") AND (state != resolved OR verified) AND (priority = 1 OR 2)) OR (assigned_to = lihong OR gonzales OR nougareau OR akamoto)

set session = GetSession

Dim users 
ReDim users(3) ' This sets up an array of four elements 
users(0) = "lihong"
users(1) = "gonzales"
users(2) = "nougareau"
users(3) = "akamoto" 

Dim priority_levels
ReDim priority_levels(1) ' This sets up an array of two elements 
priority_levels(0)  = "1"
priority_levels(1)  = "2"

Dim states
ReDim states(1)
states(0)  = "resolved"
states(1)  = "verified"

set querydef = session.BuildQuery("defect") 
querydef.BuildField("id") 
querydef.BuildField("component") 
querydef.BuildField("priority") 
querydef.BuildField("assigned_to.login_name") 
querydef.BuildField("headline") 

set operator = querydef.BuildFilterOperator(AD_BOOL_OP_OR)
set operator2 = operator.BuildFilterOperator(AD_BOOL_OP_AND)
operator2.BuildFilter "planned_release", AD_COMP_OP_EQ, "beta"
operator2.BuildFilter "state", AD_COMP_OP_NOT_IN, states 
operator2.BuildFilter "priority", AD_COMP_OP_IN, priority_levels 

operator.BuildFilter "assigned_to",AD_COMP_OP_IN, users 

set resultset = session.BuildResultSet(querydef)

Perl

# ((planned_release = "beta") AND (state != resolved OR verified) AND 

(priority = 1 OR 2))
# OR
# (assigned_to = lihong OR gonzales OR nougareau OR akamoto)

$session = $entity->GetSession();

@users = ("lihong", "gonzales", "nougareau", "akamoto");
@priority_levels = ("1", "2");
@states = ("resolved", "verified");
@planned_release = ("beta");

$querydef = $session->BuildQuery("defect"); 
$querydef->BuildField("id"); 
$querydef->BuildField("component"); 
$querydef->BuildField("priority"); 
$querydef->BuildField("headline"); 

$operator = $querydef->BuildFilterOperator($CQPerlExt::CQ_BOOL_OP_OR);
$operator2 = $operator->BuildFilterOperator($CQPerlExt::CQ_BOOL_OP_AND);
$operator2->BuildFilter ("planned_release", $CQPerlExt::CQ_COMP_OP_EQ, 
\@planned_release);
$operator2->BuildFilter ("state", $CQPerlExt::CQ_COMP_OP_NOT_IN, \@states); 
$operator2->BuildFilter ("priority", $CQPerlExt::CQ_COMP_OP_IN, 
\@priority_levels); 

$operator->BuildFilter ("assigned_to",$CQPerlExt::CQ_COMP_OP_IN, \@users);

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

VBScript

The following example finds the users in a certain group (software engineering, sw_eng).

set session = GetSession

set querydef = session.BuildQuery("users") 
querydef.BuildField("login_name") 


set operator = querydef.BuildFilterOperator(AD_BOOL_OP_AND)
operator.BuildFilter "group.name", AD_COMP_OP_EQ, "sw_eng" 


set resultset = session.BuildResultSet(querydef)

VBScript

The following example finds the default settings for when a user, John Doe (johndoe), submits a record. In this example, certain field values are in a database table named defect (for the defect record type). This code builds a SQL query.

set session = GetSession

set resultset = session.BuildSQLQuery("select project, component,_
                 severity from defect where user='johndoe'")