Skip to main content

Predefined Queries

In the SQL Syntax, We talked about the VucanSQL provide SQL syntax with templating for making the SQL logistic more flexible.

But it’s not all of it, VulcanSQL also provides req tag that you can predefine query, retrieve results and use them in your SQL.

Using req tag​

You should use req tag with {% ... %} , the syntax like the below:

-- <variable-name> is the name of your predefined query
{% req <variable-name> %}
    -- your Query SQL statement
{% endreq %}:

Let's see an example:

-- example 1: keep the statement 'select * from users' to a variable 'user'
{% req user %}
select * from users;
{% endreq %}

select * from group
where gender = {{ user.value()[0].gender }} and blacklist = {{ user.value()[0].blacklist }}

We keep the select * from users; statement to the variable user and use the user in the below SQL statement. When you call the value() method, it will send a query request to the data source for getting the result of select * from users, and that is why it is called the req ( request ) tag.

Avoid duplicated query execution​

However, it seems that we waste time sending same request two times in the above example. If you would like to send only once, you can set the predefined query results:

{% req user %}
select * from users;
{% endreq %}

-- `set` the predefined query results
{% set userData = user.value() %}

select * from group
where gender = {{ userData[0].gender }} and blacklist = {{ userData[0].blacklist }}

Using with templating language​

You can combine templating and req tag together:

{% req artist %}
  select count(*) as count from "artists" where ConstituentID = {{ context.params.id }}
{% endreq %}

{% if artist.value()[0].count == 0 %}
  {% error "Artist not found" %}
{% endif %}

select * from "artworks"
where concat(', ' , ConstituentID , ',') like concat('%, ', {{ context.params.id }} , ',%');

In the above sample, you could see we use the {% req artist %} to create an artist predefined query, which counts all artist's numbers from the dynamic parameter id got in the request.

Then use the if tag to check the count number of the artist, and use the Error Response to throw an error if it is true.

If the artist is not 0, then select all these artworks that matched the condition.

Incorrect Usage for req tag​

When using the req tag to create predefined queries, some syntax is incorrect:

🚫 Duplicated names​

You could not create predefined queries with same names, it will make the VulcanSQL build fail, below is the sample:

-- CAN NOT CREATE WITH THE SAME NAMES
{% req user %}
    select * from users;
{% endreq %}
{% req user %}
    select * from users where id = {{ context.params.id }};
{% endreq %}

🚫 Names with string format (single-quoted)​

You could not create the name with string format, it could not recognize the variable name and make the VulcanSQL build fail, sample:

-- CAN NOT CREATE WITH STRING FORMAT
{% req 'user' %}
    select * from users;
{% endreq %}

in the above, the variable name is 'user' and it’s string format, could not recognize it, the "user" is the same.

🚫 Only use req tag to get result​

You could not get the query result when only using req tag, it will be failed:

--- It's incorrect usage for querying the data
{% req artists %}
select * from "artists";
{% endreq %}

Even if you use the below syntax, it still does not get the result value:

--- Syntax Sample 1: It's incorrect usage for querying the data
{% req artists %}
select * from "artists";
{% endreq %}

{{ artists }}

--- Syntax Sample 2: return the result but is an Object
{% req artists %}
select * from "artists";
{% endreq %}

SELECT {{ artists }}

--- Syntax Sample 3: It's incorrect usage for querying the data
{% req artists %}
select * from "artists";
{% endreq %}

{{ artists.value() }}

--- Syntax Sample 4: return the result but is multiple Objects result
{% req artists %}
select * from "artists";
{% endreq %}

SELECT {{ artists.value() }}

--- Syntax Sample 5: It's incorrect usage for querying the data
{% req artists %}
select * from "artists";
{% endreq %}

{% set result = artists.value() %}
{{ result }}

--- Sample 6: Same as Sample 4,get multiple Objects result
{% req artists %}
select * from "artists";
{% endreq %}

{% set result = artists.value() %}
SELECT {{ result }}

Main Query Request

Main Query Request could let VulcanSQL know which req is the final statement and get the result directly.

Declare Main Query Request Syntax​

For declaring the Main Query Request, you should add the main in the behind of req tag variable, below is the syntax:

-- Declare the `Main Query Request`
{% req <variable-name> main %}
        -- your Query SQL statement
{% endreg %}

When you declare the SQL query is the main query, VulcanSQL will use the main query be the final statement to get the query result automatically:

-- As the final statement and get results directly to return
{% req artists main %}
select * from "artists";
{% endreq %}

In the above example, we modify the example from the “🚫 Only use req tag to get result”, and when you add the main keyword, VulcanSQL will work correctly.

Get the result from the Main Query Request​

Even when you still have other SQL clauses after the Main Query Request, it still treats the one you labeled with main as the final statement to get the result, not the last SQL clauses, below is the an example:

-- Output query results from the `Main Query Request`
{% req artists main %}
select * from "artists";
{% endreq %}

-- The SQL clauses work to get the query results because it's not the final statement.
select * from "artists" where ConstituentID = 2

Incorrect Usage of Main Query Request​

🚫 Duplicated names​

You could not create the same variable names on req tags, even if one of them is the main builder:

-- CAN NOT CREATE THE SAME NAME QUERY BUILDERS EVEN ANOTHER is MAIN BUILDER
{% req artists %}
select * from "artists" where ConstituentID = 2;
{% endreq %}

{% req artists main %}
select * from "artists";
{% endreq %}

🚫 Names with string format (single-quoted)​

You could not create the Main Query Request with string format:

-- CAN NOT CREATE MAIN QUERY REQUEST BY STRING FORMAT
{% req 'user' main %}
    select * from users;
{% endreq %}

in the above, the variable name is 'user' and it is string format, could not recognize it, the "user" is the same.

🚫 Create multiple Main Query Requests in one SQL file​

You could not create multiple main query requests in the file:

-- CAN NOT CREATE MULTIPLE MAIN QUERY REQUESTS IN THE FILE
{% req artists main %}
select * from "artists";
{% endreq %}

{% req artist2 main %}
select * from "artists" where ConstituentID = 2;
{% endreq %}

Introduing SQL Builder​

Besides predefining queries with plain statements, you can also use SQL Builder, which will be released in following releases.

SQL builder is a fluent API, that provides you multiple methods to build a SQL with a more readable way.

Let's see an example:

{% req user %}
    select * from public.users where name = '{{ context.params.id }}'
{% endreq %}

{% req orders %}
  select * from public.orders
{% endreq }

-- constructing sql statement with a fluent API
{% req result main %}
    orders
        .where('userId', user.value().id )
        .andWhere('price','>', '10000')
        .select()
                .orderBy('price', 'desc')
                .limit(10)
{% endreq %}

We're still working on it, and if you’re excited for SQL Builder, please be looking forward to our next version 😉