getCustomQueryResultsByName()

const queryName = "Some query name";
const customQueryData = {
    q: `[Column Name] eq 'Column Value'`,
};
 
const res = await vvClient.customQuery.getCustomQueryResultsByName(queryName, customQueryData)

Overview

getCustomQueryResultsByName() is a method in the VisualVault Node.js vvClient wrapper that retrieves the results of running a query previously defined either in the VisualVault Database or the VisualVault Form Database.

Use Cases

Streamline Complex Data Queries: Execute queries involving multiple component types, such as documents, users, etc. This allows for seamless cross-referencing of data within the VisualVault system.
Bulk Data Retrieval: Fetch up to 2000 records that match a specific query or use pagination to retrieve even more results.
Dynamic SQL Querying: Execute queries dynamically by supplying SQL parameter values at runtime, enabling highly flexible and customizable data retrieval.

Method Signature

vvClient.customQuery.getCustomQueryResultsByName(queryName, customQueryData)

Parameters

Name	Type	Description
`queryName`	String, required	The exact name of the query in the VisualVault Database or in the VisualVault Form Database.
`customQueryData`	Object, required
↳ `limit`	Number	Limit to n records. Can be used for paging or select top. E.g.: `limit=100`, `offset=101` would be page 2 of 100 records per page.
↳ `offset`	Number	Offset is the page start record number.
↳ `q`	String	A query filter string including any OData operator to compare values. Similar to a SQL WHERE clause. For more information, check Query Syntax.
↳ `params`	Array of objects
↳ `parameterName`	String	The parameter name as defined in the SQL query. E.g.: `@paramName` has the parameter name `“paramName”`.
↳ `value`	String	The parameter value.
↳ `sort`	String	The name of the column used to sort the results.
↳ `sortdir`	String	The sort direction, `“asc”` or `“desc”`.

customQueryData Object Examples:

Filter by column name:

SELECT
  *
FROM
  [Table Name]

const customQueryData = {
    q: `[Column Name] eq 'Some Value'`,
};

Usage of SQL parameters:

SELECT
  *
FROM
  [Table Name]
WHERE
  [Column Name] = @Parameter1Name
  AND [Another Column Name] = @Parameter2Name

const customQueryData = {
    params: JSON.stringify([
        {
            parameterName: 'Parameter1Name',
            value: 'param1Value',
        },
        {
            parameterName: 'Parameter2Name',
            value: 'param2Value',
        },
    ]),
};

Sorting and paging:

SELECT
  *
FROM
  [Table Name]

const customQueryData = {
    sort: 'Column Name', 
    sortdir: 'desc', 
    limit: 100, 
    offset: 0, 
};

Response

A typical successful response includes:

Name	Type	Description
`meta`	Object	Object containing `status`, `statusMsg`, and request details.
`data`	Array	An array of form record objects. If no records match, data is an empty array.
`pagination`	Object	Object containing pagination metadata.

Response Examples

Documents query results:

{
  meta: {
    status: 200,
    statusMsg: "OK",
    method: "GET",
    href: "https://vv5dev.visualvault.com/api/v1/CityofLincoln/Phase2/customquery?q=[dlDocID] eq 'COL-Logo.png'&queryName=test",
  },
  data: [
    {
      dlpKey: 16647,
      dlId: "2433ab83-1e48-ee11-823a-819e594591c0",
      dlReleasedID: "e8f7ae83-1e48-ee11-b982-0e849d8fa1ab",
      dlUnReleasedID: null,
      dlLatestRevisionID: "e8f7ae83-1e48-ee11-b982-0e849d8fa1ab",
      dlLatestIndexedRevisionID: null,
      dlReleasedIndexedID: null,
      dlFlID: "be7b4285-7070-e911-a99f-dcf00ea290a3",
      dlDocID: "COL-Logo.png",
      dlArchive: 2,
      dlCheckedInBy: "d2de55ad-6264-ed11-a9e3-fa0c3886a992",
      dlCheckedOutBy: "63b1ed48-5f64-ed11-a9e3-fa0c3886a992",
      dlCheckedInDate: "2023-08-31T11:50:40.547Z",
      dlCheckedOutDate: "2023-08-31T15:30:13.597Z",
    },
  ],
  pagination: {
    dataType: "PagedData",
    href: "",
    first: "",
    last: "",
    limit: 200,
    next: "",
    previous: "",
    totalRecords: 1,
  },
}

Records query results:

{
  meta: {
    status: 200,
    statusMsg: "OK",
    method: "GET",
    href: "https://vv5dev.visualvault.com/api/v1/CityofLincoln/Phase2/customquery?q=[dhDocID] eq 'Applicat-000308'&queryName=Application Type Lookup Form",
  },
  data: [
    {
      dhDocID: "Applicat-000308",
      dhid: "c3ea7c27-6e0f-4ed5-a6fc-3d7ee163a41c",
      vvCreateDate: "2023-11-08T13:37:19.99Z",
      vvCreateBy: "matias.andrade@visualvault.com",
      vvCreateByUsID: "7fe449c9-2dfe-ec11-8275-0e2e23807d4b",
      vvModifyDate: "2023-11-08T13:37:19.99Z",
      vvModifyBy: "matias.andrade@visualvault.com",
      vvModifyByUsID: "7fe449c9-2dfe-ec11-8275-0e2e23807d4b",
      "application Type": "Urban Design",
      "application SubType": "Advisory Reviews",
      "application Category": "HPC",
      status: "Enabled",
      "application SubType Value": "Advisory Reviews",
    },
  ],
  pagination: {
    dataType: "PagedData",
    href: "",
    first: "",
    last: "",
    limit: 200,
    next: "",
    previous: "",
    totalRecords: 1,
  },
}

Invalid Query Name:

{
  meta: {
    status: 404,
    statusMsg: "NotFound",
    method: "GET",
    href: "https://vv5dev.visualvault.com/api/v1/CityofLincoln/Phase2/customquery?queryName=Invalid Name",
    errors: [
      {
        code: null,
        developerMessage: "Resource does not exist",
        message: "Resource does not exist",
        reason: "Resource not found error",
      },
    ],
  },
}

Missing Query Name:

{
  meta: {
    status: 400,
    statusMsg: "BadRequest",
    method: "GET",
    href: "https://vv5dev.visualvault.com/api/v1/CityofLincoln/Phase2/customquery?q=[dhDocID] eq 'Applicat-000308'&queryName=",
  },
}

Invalid Column Name:

{
  meta: {
    status: 400,
    statusMsg: "BadRequest",
    method: "GET",
    href: "https://vv5dev.visualvault.com/api/v1/CityofLincoln/Phase2/customquery?sort=Column Name&queryName=Application Type Lookup Form",
    errors: [
      {
        code: null,
        developerMessage: "Invalid expression, invalid column: 'column name', at loc:0",
        message: "Invalid expression, invalid column: 'column name', at loc:0",
        reason: "Query syntax error",
      },
    ],
  },
}

Limitations

Record Limit for getCustomQueryResultsByName() Method
The getCustomQueryResultsByName() method returns a maximum of 2000 results. Use pagination in case you need to get more results.

Performance Considerations

Large Datasets Or High Traffic Microservices

If you need to handle large datasets (e.g., more than 2000 records) or you are working in a microservice that will have a high demand consider the following approaches:

Limit the amount of returned records by filtering by the specific criteria of your use case either using the q string or adding SQL parameters to the query.
Use pagination setting a limit number to limit the returned results to a fixed amount and run getCustomQueryResultsByName() iteratively increasing the offset value to get the results in batches.
Implement a semaphore algorithm.

Best Practices

Be Specific with Queries: Define q filters to narrow down results and save bandwidth.
Know the Limits: Use pagination if you need to query for more than 2000 records.
Leverage metadata to your advantage: For example, you can sort by descending modification date to retrieve the most recently modified records first.

Related Methods

getForms()