ARGetListEntryWithMultiSchemaFields

Important

You can continue to use C APIs to customize your application, but C APIs are not enhanced to support new capabilities provided by Java APIs and REST APIs.

Description

Performs dynamic joins by querying across multiple forms (including view and vendor forms) at run time. Depending on the structure of your data, this function might also perform the following suboperations to support dynamic joins:

  • Recursive queries: If a form contains hierarchical (parent-child) data, such as manager and employee relationships, this function can use one query to search the form recursively and retrieve the complete hierarchy and related attributes. See Recursive queries.
  • Value set queries: This function can perform IN and NOT IN operations on a fixed value set and on a value set returned by a subquery. See Value set queries.
  • Aggregate functions, group by clause, and having clause: To refine the selection of data retrieved from the database and reduce the need for in-memory processing of large numbers of records, you can now apply aggregate functions along with group by and having clauses. (AR System releases prior to 7.6.02 do not support these options.)
  • Vendor form joins: Joining vendor forms with other types of forms enables a single query to access data in both AR System and external sources. See Using aggregate functions, groupBy clauses, and having clauses.

Data in each record is returned as multi-schema field/value pairs. You can limit the list to entries that match particular conditions by specifying the qualifier parameter.

For detailed structural information, see the following documents:

For examples of how to implement this function, see Retrieving-entries-from-multiple-forms.

Important

Mid Tier does not support this function. You can create API programs, however, that use it. The programs must be able to parse the user-specified query and provide appropriate parameters to the function.

Related topics

Developing-an-API-program

AR-System-C-API-functions

Privileges

The system returns information based on the access privileges of the user that you specify for the control parameter. All lists, therefore, are limited to entries that the user can access. Users must have permission for the entryId field to access and retrieve entries.

Important

If both underlying forms of an AR System join form contain the Assignee Group field (field ID 112), the join form inherits row-level access permissions from only the primary form. In queries across multiple forms, however, row-level access permissions for each form referenced in the query are evaluated separately; one form does not control access to the other.

Synopsis

#include "ar.h"
#include "arerrno.h"
#include "arextern.h"
#include "arstruct.h"

int ARGetListEntryWithMultiSchemaFields(
  ARControlStruct *control,
  ARMultiSchemaFuncQueryFromList *queryFromList,
  ARMultiSchemaFieldFuncList *getListFields,
  ARMultiSchemaQualifierStruct *qualifier,
  ARMultiSchemaSortList *sortList,
  unsigned int firstRetrieve,
  unsigned int maxRetrieve,
  ARBoolean useLocale,
  ARMultiSchemaFieldIdList *groupBy,
  ARMultiSchemaFuncQualifierStruct *having,
  ARMultiSchemaFieldFuncValueListList *entryList,
  unsigned int *numMatches,
  ARStatusList *status)

Input arguments

control

The control record for the operation. It contains information about the user requesting the operation, where to perform the operation, and which session is used to perform it. The user and server fields are required.

queryFromList

A list of items to query and the join conditions between each item. An item can be a form (schema) or a recursive query. This parameter is used to generate the query's FROM clause.

All types of forms except display-only can be joined.

Only one recursive query can be included in the list, and it must be the first item in the list. The recursive query can operate on only one form in the list. (See ARMultiSchemaRecursiveFuncQueryStruct.)

Nested queries are not supported.

You can use inner, left outer, and right outer joins. If join information between objects in the list is not specified, the result set is a Cartesian product. 
For more information, see ARMultiSchemaFuncQueryFromList.

getListFields

A list of zero or more fields to retrieve for each entry from the forms listed in the queryFromList parameter, along with the type of aggregate, if any. This parameter is used to generate the query's SELECT statement.

If no fields are specified, the fields in the Results List Fields property of each form in the queryFromList parameter are returned. If that property is empty, the form's Short Description field is returned.

The system checks the permissions for each field and returns only those fields for which the user has read access.

Because ARGetListEntryWithMultiSchemaFields can operate on multiple forms, each field in this list must be qualified by a form name or alias. Additionally, each field in this list must specify the aggregate function being applied to the field.

getListFields supports the following aggregate functions:

  • NONE
  • SUM
  • AVG
  • COUNT
  • MIN
  • MAX

Important

As of release 7.6.02, getlListFields is an ARMultiSchemaFieldFuncList structure. In earlier releases, it was an ARMultiSchemaFieldIDList structure. ARMultiSchemaFieldIDList does not support aggregate functions. If you send an ARGetListEntryWithMultiSchemaFields call that specifies aggregate functions to a pre-7.6.02 AR System server, the new options are stripped from the query.

For more information, see ARMultiSchemaFieldFuncStruct, ARMultiSchemaFieldFuncList, and ARMultiSchemaFuncQualifierStruct.

qualifier

Zero or more conditions that limit the set of entries that this function retrieves. This parameter is used to generate the query's WHERE clause. For some databases, the join qualifiers are also used to generate the WHERE clause.

A qualification can include one or more fields and any combination of conditional, relational, arithmetic (numeric data types only), and IN or NOT IN operations. IN and NOT IN operations can be performed on a fixed value set or on a value set returned by a subquery.

The system generates an error if the user does not have read permission for a field or if a field does not exist. To retrieve all entries, set this parameter to NULL or set its operation element to 0 (AR_COND_OP_NONE).

See ARMultiSchemaQualifierStruct.

sortList

A list of zero or more fields that specifies the entry sort order. This parameter is used to generate the primary query's ORDER BY clause. This parameter cannot be used in subqueries.

If no field is specified, the system sorts the entries according to the first form's default sort order. The system generates an error if you do not have read access to all specified fields.

See ARMultiSchemaSortList.

Important

For recursive queries, set the sortList parameter to NULL. See Recursive queries.

firstRetrieve

The first entry to retrieve. A value of 0 (AR_START_WITH_FIRST_ENTRY) represents the first entry.  A value of 1 will skip the first entry. 

maxRetrieve

The maximum number of entries to retrieve. Use this parameter to limit the amount of data returned if the qualification does not sufficiently narrow the list. To not assign a maximum, specify 0 (AR_NO_MAX_LIST_RETRIEVE).

useLocale

A flag that indicates whether to search for entries based on the locale.

If you specify 1 (TRUE) and the Localize Server option is selected, entries are searched using the locale specified in AR_RESERV_LOCALE_LOCALIZED_SCHEMA. If no matches are found for the specified locale, the search becomes less restrictive until a match is found.

If you specify 0 (FALSE) or the Localize Server option is cleared, all entries are searched. For more information, see Localizing-the-server.

groupBy

A list of zero or more fields used to partition the result set. This parameter is used to generate the primary query's GROUP BY clause. This field can be null. If no fields are specified, no GROUP BY clause is generated.

A common use for GROUP BY is to create partitions in the result set over which the database server computes an aggregate function.

For example: Consider a table of ticket IDs, ticket statuses, and agent names. A SQL query to discover the number of tickets open for each agent would look like:

SELECT COUNT(ticketId),agent FROM tickets WHERE status='Open' GROUP BY agent

Important

If you send an ARGetListEntryWithMultiSchemaFields call with a non-empty groupBy list to a pre-7.6.0.2 AR System server, the GROUP BY clause is stripped from the query.

The following rules apply to the groupBy parameter:

  • If groupBy is specified, all fields in the select list must be aggregates or included in the groupBy list.
  • Aggregate fields and sub-queries (selects) might not appear in the groupBy list.
  • Fields might appear in the groupBy list that are not in the select list.
  • Fields used in the groupBy list need not be in the having list and fields used in the having list need not be in the groupBy list.

having

Used to generate the primary query's HAVING clause. This field can be null. If null or an empty qualifier is specified, no HAVING clause is generated.

The HAVING clause filters the result set. Unlike the qualifier, it can specify aggregate functions.

For example:  The query to illustrate the groupBy clause to further limit the result list to agents with more than 10 tickets open, would look like:

SELECT COUNT(ticketId),agent FROM tickets WHERE status='Open' GROUP BY agent HAVING COUNT(ticketId) > 10

Important

If you send an ARGetListEntryWithMultiSchemaFields call with a non-empty having qualifier to a pre-7.6.0.2 AR System server, the HAVING clause is removed from the query.

The following rules apply to the having parameter:

  • Numeric or enumerated fields must be used for any function other than COUNT.
  • Aggregate and non-aggregate fields can be in the having qualifier.
  • Fields might appear in the having qualifier that are not in the select list.
  • Fields used in the groupBy list do not have to be in the having clause and fields used in the having clause do not need to be in the groupBy list.

Return values

entryList

A list of zero or more (accessible) entries that match the criteria specified in the qualifier parameter. The system returns an ARMultiSchemaFieldFuncValueListList, in which each field contains the queryAlias, fieldId, and funcID. The list contains zero items if no entries match the specified criteria.

numMatches

The total number of (accessible) entries that match the qualification criteria. This value does not represent the number of entries returned unless the number of matching entries is less than or equal to the maxRetrieve value. If you do not want to retrieve this value, set this parameter to NULL.

Important

Performing this count requires additional search time if the number of matching entries is more than the maxRetrieve value. In this case, the cost of completing the search diminishes the performance benefits of retrieving fewer entries.

status

A list of zero or more notes, warnings, or errors generated from a call to this function. For a description of all possible values, see Error-checking.

 

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