Querying Existing Query Results

The Query Results Data Source (QRDS) lets you run queries against results from your other Data Sources. Use it to join data from multiple databases or perform post-processing. We use uses an in-memory SQLite database to make this possible. As a result, queries against large result sets may fail if the application runs out of memory.

QRDS

1. Querying Existing Query Results

The QRDS accepts SQLite query syntax:

SELECT
	a.name,
	b.count 
FROM query_123 AS a 
JOIN query_456 AS b
  		ON a.id = b.id

Your other queries are like “tables” to the QRDS. Each one is aliased as query_ followed by its query_id which you can see in the URL bar of your browser from the query editor. For example, a query at /queries/49588 has the alias query_49588.

The query alias like query_49588 must appear on the same line as its associated FROM or JOIN keyword.

2. Querying Existing Parameterized Query Results

When you're working with the application and you want to reference a query that has parameters, you use a special format. Let's delve deeper into it.

Format:

param_query_<query_id>_{<URL ENCODED KEY=VALUE PARAMETER STRING>}

1. param_query_: This prefix signals to the application that what follows is a query with parameters.

2. <query_id>: A unique identifier for your query in the application. For instance, if the query URL in the application ends with /queries/45, the ID is 45.

3. {<URL ENCODED KEY=VALUE PARAMETER STRING>}: Here, you insert the parameters for the query. These parameters should be URL encoded, which translates special characters into a format safe for online transmission.

Example:

Let's say you're querying the parameterized query below (its ID is 461):

You want to querying these existing parametrized query results using the Query Results Data Source (QRDS). You will create a new query as below:

SELECT * FROM param_query_461_{contract_address="0x865377367054516e17014ccded1e7d814edc9ce4"&end_block=17838912&event_name="Transfer"&start_block=17838000}

Explanation:

• param_query_461_: This means you're calling a specific, predefined parametrized query in the application with the ID 461.

• {contract_address="0x865377367054516e17014ccded1e7d814edc9ce4"&end_block=17838912&event_name="Transfer"&start_block=17838000}: This is where you add your parameters.

Parameter (URL ENCODED KEY)	Value (VALUE PARAMETER STRING)
contract_address	0x865377367054516e17014ccded1e7d814edc9ce4
end_block	17838912
event_name	Transfer
start_block	17838000

In this example, contract_address and event_name are of text type and should be enclosed in quotation marks (""). end_block and start_block are of number type. The order of the parameters inside the open curly bracket doesn't matter.

Important remark on URL Encoding:

When a parameter value has special characters or spaces, it should be URL encoded. For example, if your value parameter string is John Doe, the space in "John Doe" is converted to %20 (John%20Doe).

This page provides a comprehensive list of characters and their corresponding URL encoded values: https://www.w3schools.com/tags/ref_urlencode.asp

Note: Always make sure that the parameter names you use match the names defined in the original query in the application.

3. Cached Query Results

When you query the Query Results Data Source, the application executes the underlying queries first. This guarantees recent results in case you schedule a QRDS query. You can speed up QRDS queries by using cached_query_ for your query aliases instead of query_. This tells the application to use the cached results from the most recent execution of a given query. This improves performance by using older data. You can mix both syntaxes in the same query too:

SELECT
	a.name,
	b.count 
FROM cached_query_123 AS a 
JOIN query_456 AS b
  		ON a.id = b.id

4. Query Results Permissions

Access to the Query Results Data Source is governed by the groups it’s associated with like any other Data Source. But the application will also check if a user has permission to execute queries on the Data Sources the original queries use.

As an example, a user with access to the QRDS cannot execute SELECT * FROM query_123 if query 123 uses a data source to which that user does not have access. They will see the most recently cached QRDS query result from the query screen in the application. But they will not be able to execute the query again.