Skip to content

SQL

The documentation applies to:✅ v0.8.0

Preparation knowledge

We recommend you to read carefully some sections below before you read more steps on this document:

  • Page concept link
  • State Management link
  • Global variables link

SQL

SQL is very popular and easy to learn. However, we point out that is very difficult to add some customs on one SQL command such as SELECT or INSERT. Unlike MongoDB, SQL is flexible when compiling a lot of chaining SELECT (aka sub query). Therefore, we decide to separate some customs based on use-cases.

Interpolation {{}}

Same as MongoDB, we use {{data.id}} to translate a value. However, SQL Injection is very easy to cheat so that we always apply SQLParameter instead of concat string.

One important thing that you need to add |{type} in your interpolation for indicating with database type should be bound. There are some primary types of each database:

Declaration C# type MySQL PostgreSQL SQL Server
{{data.id}} string LongText Text NVarChar
{{data.date|date}} DateTime Datetime Date DateTime
{{data.allow|bool}} bool Bit Boolean Bit
{{data.counter|int}} int Int32 Integer Int
{{data.tick|long}} long Int64 Bigint BigInt
{{data.timestamp|timespan}} Timespan Int64 Bigint BigInt
{{data.money|float}} float Float Double Float
{{data.money|double}} double Double Double Float
{{data.money|decimal}} decimal Decimal Money Decimal

Also, it is applied for Built-in methods.

SQL - Dynamic List

SELECT * FROM table_name

By default, you don't want to add some custom words as Simplest Query above because Dynamic List will combine automatically Filters and Pagination. However, in case you want to have a complexity query such as UNION or multiple SELECT, LET Portal provides some custom words to do flexible.

On Dynamic List, we use this common SELECT with some customs keywords below:

SELECT * FROM "table_name" WHERE "your_condition" 
    AND {{SEARCH}} AND {{FILTER}} 
    ORDER BY {{ORDER}} 
    LIMIT {{PAGENUM}} 
    OFFSET {{PAGESTART}}

Interpolation {{ }}

Please remember that you want to bind some data into SQL, you should use {{ }}, also apply for some custom words above

Now back to our customs words for Dynamic List, we will discuss one by one for letting you know what exactly data will be replaced by custom word

Multiple words

You can put many custom words whenever you want. However, you ensure to put correct place.

This word will generate a condition of searching keyword on Dynamic List. For example: you want to find an username on users table, so you enter a keyword admin on Dynamic List.

Default

Default tab means this is a query you need to input on Dynamic List Builder page.

SELECT id, username FROM users WHERE {{SEARCH}}
SELECT id, username FROM users WHERE username LIKE '%@keyword%'
/* In this, @keyword = 'admin'*/

{{FILTER}}

This word will generate a combination of Advanced Filter on Dynamic List. For example: you want to find any user has username contains admin and createdDate must be greater than 2020-02-02

SELECT id, username, createdDate FROM users WHERE {{FILTER}}
SELECT id, username, createdDate FROM users WHERE username LIKE '%@filter1%' AND createdDate >= @filter2

/* In this, @filter1 = 'admin' and @filter2 = '2020-02-02' */

{{ORDER}}

This word will generate a sorting string on Dynamic List. For example: you want to sort username ascending on Dynamic List.

SELECT id, username FROM users ORDER BY {{ORDER}} 
SELECT id, username FROM users ORDER BY username ASC

{{PAGENUM}} and {{PAGESTART}}

Two words will replace two numbers number per page and skip rows number. For example: you want to take top 10 rows on page 2 on Dynamic List.

SELECT id, username FROM users LIMIT {{PAGENUM}} OFFSET {{PAGESTART}}
SELECT id, username FROM users LIMIT 10 OFFSET 10
/* OFFSET 10 is skipping first 10 rows on result*/

Due to different pagination implementation of each database, we just provide two words for helping you to add a correct syntax. There are pagination implementation of three databases:

SELECT id, username FROM users 
OFFSET {{PAGESTART}} ROWS FETCH NEXT {{PAGENUM}} ROWS ONLY
SELECT id, username FROM users LIMIT {{PAGENUM}} OFFSET {{PAGESTART}}
SELECT id, username FROM users LIMIT {{PAGENUM}} OFFSET {{PAGESTART}}

SQL - Action Button

For using SQL on Action Button (both Standard, Dynamic List and Page), you can run SQL for CRUD data

SELECT * FROM users
INSERT INTO users(username,password) VALUES ("{{data.username}}", "{{data.password}}")
UPDATE users SET username = "{{data.username}}" WHERE id = "{{data.id}}"
DELETE FROM users WHERE id = "{{data.id}}"

LET Portal still support multiple execution steps, so you can read to get more details.

SQL - Chart

Unlike SQL - Dynamic List, Chart is supporting two words are {{FILTER}} and {{REAL_TIME}}.

Real-time chart

Unlike MongoDB, {{REAL_TIME}} is a mandatory word for real-time chart

{{FILTER}}

Like as SQL - Dynamic List, this word is used to generate a combination of chart filters.

For example: you want to find a report has reportedDate around NOW and NOW - 30 days

SELECT id, reportedDate FROM reports WHERE {{FILTER}}
SELECT id, reportedDate FROM reports 
WHERE reportedDate >= @filter1 && reportedDate < @filter2

/* In this, @filter1 = 'NOW - 30days' and @filter2 = 'NOW' */

{{REAL_TIME}}

This is a mandatory keyword when you set up a real-time Chart. You need to ensure comparerealtimefield is a date column.

For example: you want to compare reportedDate between `

SELECT id, reportedDate FROM reports WHERE {{REAL_TIME}}
SELECT id, reportedDate FROM reports 
WHERE reportedDate >= '2020-02-02 00:00:00' AND reportedDate < '2020-02-02 00:00:30'

For a reason why a duration is 30 seconds, let check it