---
id: predefined-queries
---

# Predefined Queries

In the [SQL Syntax](./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:

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

Let's see an example:

```sql
-- 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:

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

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

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:

```sql
{% 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](./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:

```sql
-- 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:

```sql
-- 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:

```sql
--- 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:

```sql
--- 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:

```sql
-- 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**:

```sql
-- 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:

```sql
-- 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:

```sql
-- 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:

```sql
-- 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:

```sql
-- 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:

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

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

// highlight-start
-- 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 %}
// highlight-end

```

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