C example--recursive queries

The example shows you how to use a recursive query and update an existing code to use the groupBy and having clauses and related structures. Lines with required code changes are marked with //.

For information about using the groupBy and having clauses and the related aggregate functions, see Using aggregate functions, groupBy clauses, and having clauses.

Related topic

Retrieving-entries-from-multiple-forms

In the following example you will use a recursive query to find all employees who either report directly to Bob Jones or who report to his direct reports:

#define EMPLOYEE_FORM "Employee"
#define EMPLOYEE_ALIAS "Employee"
#define EMP_EMPLOYEE_NAME 536870913
#define EMP_EMPLOYEE_ID 536870914
#define EMP_MANAGER_ID 536870915
#define EMP_MANAGER_ID 536870915
void queryOnEmployeeForm(ARControlStruct *ctrl, ARStatusList *status)
{
  ARMultiSchemaFieldFuncValueListList msEntryList = {0, NULL};  /**/
  unsigned int numMatches;
  unsigned int *numMatchesPtr;
  int result;
  ARMultiSchemaFuncQueryFromList fromList = {0, NULL};  /**/
  ARMultiSchemaSortList sortList = {0, NULL};
  ARMultiSchemaFieldFuncList getListFields = {0, NULL};  /**/
  ARMultiSchemaFuncQueryFromStruct fromItem = {0,}; /* 0-fill*/ /**/
  ARMultiSchemaRecursiveFuncQueryStruct recursiveQuery = {"",}; /* 0-fill */ /**/
  ARMultiSchemaFuncQueryFromStruct recqueryfromItem = {0,};  /* 0-fill */ /**/
  ARMultiSchemaQualifierStruct qual = {AR_COND_OP_NONE, NULL};
  ARMultiSchemaQualifierStruct newQual = {AR_COND_OP_NONE, NULL};
  ARMultiSchemaQualifierStruct qualifier = {AR_COND_OP_NONE, NULL};
  ARMultiSchemaQualifierStruct startWith = {AR_COND_OP_NONE, NULL};
  ARMultiSchemaQualifierStruct connectBy = {AR_COND_OP_NONE, NULL};
  ARMultiSchemaRelOpStruct swRelop, relop;
  ARMultiSchemaRelOpStruct cbRelop;
  ARMultiSchemaFieldFuncStruct fieldIdRecQuery[4] = {{"",},}; /* 0-fill */ /**/
  ARMultiSchemaFieldFuncStruct fieldIdMain[4] = {{"",},}; /* 0-fill*/ /**/
  /* fill up query struct */
  fromList.numItems = 1;
  fromList.listPtr = &fromItem;
  strcpy(fieldIdMain[0].queryFromAlias, "recquery");
  fieldIdMain[0].fieldId = EMP_EMPLOYEE_NAME; /* employee name */
  fieldIdMain[0].funcId = AR_MULTI_SCHEMA_FUNC_NONE;  /**/
  strcpy(fieldIdMain[1].queryFromAlias, "recquery");
  fieldIdMain[1].fieldId = EMP_EMPLOYEE_ID; /* employee id */
  fieldIdMain[1].funcId = AR_MULTI_SCHEMA_FUNC_NONE;  /**/
  strcpy(fieldIdMain[2].queryFromAlias, "recquery");
  fieldIdMain[2].fieldId = EMP_MANAGER_ID; /* manager id */
  fieldIdMain[2].funcId = AR_MULTI_SCHEMA_FUNC_NONE;  /**/
  getListFields.numItems = 3;
  getListFields.listPtr = fieldIdMain;  /**/
  fromItem.type = AR_MULTI_SCHEMA_RECURSIVE_QUERY;
  fromItem.u.recursiveQuery = &recursiveQuery;
  fromItem.joinType = 0;
  fromItem.joinQual = NULL;
  strcpy(fromItem.queryFromAlias, "recquery");
  /* The following lines specify the form on which the recursive query */
  /* operates. They also specify a recursive schema alias for that form. */
  recursiveQuery.queryFromList.numItems = 1;
  recursiveQuery.queryFromList.listPtr = &recqueryfromItem;
  recursiveQuery.getListFuncs.numItems = 3;
  strcpy(fieldIdRecQuery[0].queryFromAlias, EMPLOYEE_ALIAS);
  fieldIdRecQuery[0].fieldId = EMP_EMPLOYEE_NAME; /* employee name */
  fieldIdRecQuery[0].funcId = AR_MULTI_SCHEMA_FUNC_NONE;  /**/
  strcpy(fieldIdRecQuery[1].queryFromAlias, EMPLOYEE_ALIAS);
  fieldIdRecQuery[1].fieldId = EMP_EMPLOYEE_ID; /* employee id */
  fieldIdRecQuery[1].funcId = AR_MULTI_SCHEMA_FUNC_NONE;  /**/
  strcpy(fieldIdRecQuery[2].queryFromAlias, EMPLOYEE_ALIAS);
  fieldIdRecQuery[2].fieldId = EMP_MANAGER_ID; /* manager id */
  fieldIdRecQuery[2].funcId = AR_MULTI_SCHEMA_FUNC_NONE;  /**/
  recursiveQuery.getListFuncs.listPtr = fieldIdRecQuery; /**/
  recursiveQuery.startQual = &startWith;
  recursiveQuery.recursionQual = &connectBy;
  /* The following line specifies the number of levels in the hierarchy to */
  /* retrieve. Specify 3 to retrieve 2 levels below the parent level. (The */
  /* parent level is identified by the startQual parameter.) */
  recursiveQuery.levelsToRetrieve = 3;
  strcpy(recursiveQuery.recursiveSchemaAlias, EMPLOYEE_ALIAS);
  recqueryfromItem.type = AR_MULTI_SCHEMA_SCHEMA_NAME;
  strcpy(recqueryfromItem.u.schemaName, EMPLOYEE_FORM);
  recqueryfromItem.joinType = 0;
  recqueryfromItem.joinQual = NULL;
  strcpy(recqueryfromItem.queryFromAlias, EMPLOYEE_ALIAS);
  startWith.operation = AR_COND_OP_REL_OP;
  startWith.u.relOp = &swRelop;
  swRelop.operation = AR_REL_OP_EQUAL;
  swRelop.operandLeft.tag = AR_FIELD;
  strcpy(swRelop.operandLeft.u.fieldId.queryFromAlias, EMPLOYEE_ALIAS);
  swRelop.operandLeft.u.fieldId.fieldId = EMP_EMPLOYEE_NAME;
  swRelop.operandRight.tag = AR_VALUE;
  swRelop.operandRight.u.value.dataType = AR_DATA_TYPE_CHAR;
  swRelop.operandRight.u.value.u.charVal = "Bob Jones";
  /* The following lines specify the recursion qualifier. */
  connectBy.operation = AR_COND_OP_REL_OP;
  connectBy.u.relOp = &cbRelop;
  cbRelop.operation = AR_REL_OP_EQUAL;
  cbRelop.operandLeft.tag = AR_FIELD;
  strcpy(cbRelop.operandLeft.u.fieldId.queryFromAlias, EMPLOYEE_ALIAS); /* child */
  cbRelop.operandLeft.u.fieldId.fieldId = EMP_MANAGER_ID; /* manager id */
  cbRelop.operandRight.tag = AR_FIELD;
  strcpy(cbRelop.operandRight.u.fieldId.queryFromAlias,
        AR_REC_QRY_PARENT_ALIAS); /* parent */
  cbRelop.operandRight.u.fieldId.fieldId = EMP_EMPLOYEE_ID; /* employee id */
  numMatchesPtr = &numMatches;
  /* Call routine */
  result = ARGetListEntryWithMultiSchemaFields (ctrl, &fromList,
        &getListFields, &qualifier, &sortList, AR_START_WITH_FIRST_ENTRY,
        AR_NO_MAX_LIST_RETRIEVE, FALSE,
        NULL, NULL, /* groupBy, having */ /**/
        &msEntryList, numMatchesPtr, status);
}

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*