Saved queries via API

The standard parameters listed above are special because Queries view in FA Back provides input components that you can use to make them convenient in the UI. Still, these parameters might not be sufficient. For example, you might want to provide a list of transaction type codes or contact IDs. You also might want to use any other text or number value that corresponds to a column you want to filter by. With custom parameters, you can provide arbitrary text or number arguments to your custom query.

Custom parameters in our GraphQL endpoints work differently to those in FA Back queries. In FA Back, you can append custom parameters to queries as they are. In GraphQL endpoints, custom parameters have limitations and the values are sanitized before being used in the query.

You can provide custom parameters as numbers, text, column references, keywords, or lists of numbers or text. Text parameters are automatically passed to the query as text input. The FA system allows the following characters in custom parameters: unicode letters, numbers, dashes (—), underscores (_), at signs (@), dots (.), percentage signs (%), and spaces. Other characters are blocked.

Column references and keywords are special cases: You can append them to the query as they are, but their content is restricted. Column references must be alphanumerics surrounded by backticks (`), and we only allow the following keywords: AND, OR, IN, =, ASC, DESC. Since keywords are passed as they are, you can't use them as normal text parameters.

For an example custom parameter, see Paginated transaction list.

Perhaps you want to only return the representative information of a specific contact or to only return information about contacts with the status “Closed”. You can adjust the parameters of the query shown in Listing and running queries to achieve this:

SELECT c.id AS contacDbtId, rep.id AS representativeDbId, rep.name AS representativeName, rep.contact_id AS representativeContactId
FROM pm2_contact c 
LEFT JOIN pm2_contact_representatives reps ON reps.pm2_contact = c.id 
LEFT JOIN pm2_contact rep ON reps.representatives = rep.id
WHERE c.id = $P(contactId)

Next, provide the ID of a specific contact for which the query should return the representatives:

query{
  savedQuery(queryId:1716983261054){
    queryResult(queryParameters:{contactId:1234}){
      queryResultJson
    }
  }
}

You could also limit the query based on status by adjusting the query as follows:

SELECT c.id AS contactDbId, rep.id AS representativeDbId, rep.name AS representativeName, rep.contact_id AS representativeContactId
FROM pm2_contact c 
LEFT JOIN pm2_contact_representatives reps ON reps.pm2_contact = c.id 
LEFT JOIN pm2_contact rep ON reps.representatives = rep.id
WHERE c.status = $P(status)

Now, run the query with a custom parameter:

query{
  savedQuery(queryId:1716983261054){
    queryResult(queryParameters:{customParameters:{status:"P"}}){
      queryResultJson
    }
  }
}

You could further filter the results for multiple statuses, for example, both passive and closed contacts. For this, you could adjust the query as follows:

SELECT c.id AS contactDbId, rep.id AS representativeDbId, rep.name AS representativeName, rep.contact_id AS representativeContactId
FROM pm2_contact c 
LEFT JOIN pm2_contact_representatives reps ON reps.pm2_contact = c.id 
LEFT JOIN pm2_contact rep ON reps.representatives = rep.id
WHERE c.status IN $P(statusList)

Again, run the query with a custom parameter:

query{
  savedQuery(queryId:1716983261054){
    queryResult(queryParameters:{customParameters:{statusList:["P", “C”]}}){
      queryResultJson
    }
  }
}

Note that you must provide every parameter in your query also when you run it via the queryResult endpoint. FA doesn’t directly support default parameter values for saved queries.

You can also treat a query parameter as optional. For example, you can adjust the previous query to contain optional "juridical form" and status list conditions:

SELECT c.id AS contactDbId, rep.id AS representativeDbId, rep.name AS representativeName, rep.contact_id AS representativeContactId
FROM pm2_contact c 
LEFT JOIN pm2_contact_representatives reps ON reps.pm2_contact = c.id 
LEFT JOIN pm2_contact rep ON reps.representatives = rep.id
WHERE (-1 IN $P(statusList) OR c.status IN $P(statusList))
AND ($P(juridicalForm) = -1 OR c.juridical = $P(juridicalForm))

Now the user can only provide a list of statuses and a juridical form. If the user wants to filter based on status or juridical form, they can simply pass the other parameter as -1 (or [-1] for the list parameter), in which case the condition always passes.

Note that standard parameters work differently when there are empty parameter values. Missing standard parameters are read as null values, meaning an optional date range selection looks as follows:

SELECT id AS transactionDbId FROM pm2_transaction
WHERE ($P(startDate) IS NULL OR $P(startDate) <= transaction_date)
AND ($P(endDate) IS NULL OR $P(endDate) >= transaction_date)

In this case, startDate and/or endDate can be omitted entirely from the GraphQL request.

The Advanced data retrieval with GraphQL queries section describes combining SQL queries with objects in the GraphQL context. To do this, you could adjust one of the previous examples as follows:

SELECT c.id AS contactDbId, rep.id AS representativeDbId, rep.name AS representativeName, rep.contact_id AS representativeContactId
FROM pm2_contact c 
LEFT JOIN pm2_contact_representatives reps ON reps.pm2_contact = c.id 
LEFT JOIN pm2_contact rep ON reps.representatives = rep.id
WHERE c.id IN $P(contactDbIds)

With this adjusted query, you could fetch the representatives for a list of contactDbId fields. You could also run nested GraphQL query operations on the contactDbIds, for example, to fetch the total value of their current assets under FA management. This updated query is run as follows:

query{
  savedQuery(queryId:1716983261054){
    queryResult(queryParameters:{customParameters:{contactDbIds:[15, 28, 17]}}){
      queryResultJson,
      queryResultContacts{
        id,
        name,
        portfolioReport{
          marketValue
        }
      }
    }
  }
}

The response is as follows:

{
  "data": {
    "savedQuery": {
      "queryResult": {
        "queryResultJson": [
          {
            "contactDbId": 15,
            "representativeDbId": 90,
            "representativeName": "Aaron Advisor",
            "representativeContactId": "aaron"
          },
          {
            "contactDbId": 28,
            "representativeDbId": 10527108,
            "representativeName": "Adam Advisor",
            "representativeContactId": "advisor"
          },
          {
            "contactDbId": 17,
            "representativeDbId": 10527108,
            "representativeName": "Adam Advisor",
            "representativeContactId": "advisor"
          },
          {
            "contactDbId": 15,
            "representativeDbId": 10527108,
            "representativeName": "Adam Advisor",
            "representativeContactId": "advisor"
          }
        ],
        "queryResultContacts": [
          {
            "id": 17,
            "name": "Colin Customer",
            "portfolioReport": null
          },
          {
            "id": 28,
            "name": "Carla Customer",
            "portfolioReport": {
              "marketValue": 20754.212
            }
          },
          {
            "id": 15,
            "name": "John Doe",
            "portfolioReport": {
              "marketValue": 2765472.97
            }
          }
        ]
      }
    }
  }
}

If you choose to fetch all the information based on the linked object, you don’t need to return queryResultJson . In the example case above, however, including the representative information in the SQL query itself is more efficient than fetching it from the linked object (see Improving performance with saved queries). 

In other cases, performance improvements are not significant, and you can build your API call solely on the objects whose IDs the SQL query returns. For this approach, you can simplify the SQL query to only return the contactDbId column or a similar column for other data types.

Some types of GraphQL queries can suffer from poor performance. In general, performance decreases when using GraphQL to list a large number of objects and drilling into those objects to load additional data located in a different database table.

You might want to use the contacts endpoint to list contacts, their names, and the names of their representatives. You can do this easily with GraphQL, but increasing the number of returned contacts makes the query increasingly inefficient. This is because N+1 separate queries are made to the database. First, a single query finds all contacts (fast). Then, the server runs an additional query for each returned contact to find their representatives (slow with a significant number of contacts). No additional queries are needed for the properties of a contact (for example, their name), but the representative’s name doesn’t belong to the contact itself. The same pattern applies to most queries that combine data from separate objects. The following is an example query that works but suffers from the N+1 problem:

query{
  contacts(status:"A"){
    id
    name
    contactId
    representatives{
      id
      name
      contactId
    }
  }
}

Such a query only takes about a second with a few hundred contacts, but the time increases linearly as the number of contacts grows. With a thousand contacts, such a GraphQL query takes too long for most synchronous operations.

Instead, the same information can be fetched with a direct SQL query which shortens the response time to a fraction of a second when dealing with tens of thousands of contacts. You could use an SQL query as follows:

SELECT c.id AS contactDbId, c.contact_id AS contactId, c.name AS contactName, rep.id AS representativeId, rep.name AS representativeName, rep.contact_id AS representativeContactId
FROM pm2_contact c 
LEFT JOIN pm2_contact_representatives reps ON reps.pm2_contact = c.id 
LEFT JOIN pm2_contact rep ON reps.representatives = rep.id
WHERE c.status = "A"

Next, run the query (assuming 12345 is the saved query ID):

query{
  savedQuery(queryId:12345){
    queryResult{
      queryResultJson
    }
  }
}

The response is as follows:

{
  "data": {
    "savedQuery": {
      "queryResult": {
        "queryResultJson": [
          {
            "contactDbId": 10551484,
            "contactId": "STEWIE19-23066",
            "contactName": "Stewart Downing 23066",
            "representativeId": 10527108,
            "representativeName": "Adam Advisor",
            "representativeContactId": "advisor"
          },
          {
            "contactDbId": 10551483,
            "contactId": "STEWIE19-23065",
            "contactName": "Stewart Downing 23065",
            "representativeId": 10527108,
            "representativeName": "Adam Advisor",
            "representativeContactId": "advisor"
          }, … (omitted)
          ]
      }
    }
  }
}

You might want to give your users a paginated view of their transactions for increased efficiency in transaction loading, even for portfolios with a long transaction history. The GraphQL APIs in FA don’t support this, but you can achieve it with the query API. First, you could use the following query to return a list of transactions for the given portfolios:

SELECT id AS transactionDbId 
FROM pm2_transaction 
WHERE pf_id IN $P(portfolioId) 
ORDER BY $P(orderByParam) $P(orderByDirection)
LIMIT $P(limitParam) 
OFFSET $P(offsetParam);

Next, run the query as follows:

query{
  savedQuery(queryName:"paginatedTransactions"){
    queryResult(queryParameters:{
      customParameters:{
        orderByParam:"`transaction_date`",
        orderByDirection: "DESC",
        limitParam:10,
        offsetParam:0,
      },
      portfolioIdList:[353809, 353810],
    }){
      queryResultTransactions{
        transactionDate
        id,
        typeName,
        amount,
        securityName,
        tradeAmount
      }
    }
  }
}

Note that in this example, linked transaction objects are loaded to get the query results. This is done for simplicity, but you can also include the information you want to return within the query definition itself and skip loading the linked objects.

There are some requirements for the parameters:

For safety reasons, you can’t dynamically define larger chunks of SQL via query parameters. For example, you can’t define `pf_id` DESC, `ext_id` DESC as a single parameter. Instead, you need to change the query to something like the following:

SELECT id AS transactionDbId 
FROM pm2_transaction 
WHERE pf_id IN $P(portfolioId) 
ORDER BY $P(orderByParam1) $P(orderByDirection1), $P(orderByParam2) $P(orderByDirection2), $P(orderByParam3) $P(orderByDirection3)
LIMIT $P(limitParam) 
OFFSET $P(offsetParam);

If the user doesn’t indicate three columns to sort by, you can provide a default sorting column (you can specify the same column multiple times) for the undefined parameters.

Note that to make the paginated search available to users with limited access rights, you need middleware. Users with limited visibility or extended limited visibility can’t access saved query endpoints in FA.