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

Simplest Query

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:

Full custom words

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.

{{SEARCH}}

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.

Default

SELECT id, username FROM users WHERE {{SEARCH}}

Translated

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

Default

SELECT id, username, createdDate FROM users WHERE {{FILTER}}

Translated

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.

Default

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

Translated

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.

Default

SELECT id, username FROM users LIMIT {{PAGENUM}} OFFSET {{PAGESTART}}

Translated

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:

SQL Server

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

MySQL

SELECT id, username FROM users LIMIT {{PAGENUM}} OFFSET {{PAGESTART}}

PostgreSQL

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

Query

SELECT * FROM users

Insert

INSERT INTO users(username,password) VALUES ("{{data.username}}", "{{data.password}}")

Update

UPDATE users SET username = "{{data.username}}" WHERE id = "{{data.id}}"

Delete

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

Default

SELECT id, reportedDate FROM reports WHERE {{FILTER}}

Translated

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 `

Default

SELECT id, reportedDate FROM reports WHERE {{REAL_TIME}}

Translated

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