Skip to main content

Setup bảng tạm trong CSDL

Mỗi bảng tạm yêu cầu phải có 4 cột sau (nếu dùng mọi option default):

Cột Kiểu Mục đích
RowCondition JSON (trong mariadb là LONGTEXT) Lưu phần where trong query, sử dụng 
ItemID VARCHAR/CHAR Dùng default làm id của từng hàng, không nhất thiết phải làm PRIMARY KEY nếu có ý định viết id động
ParentID VARCHAR/CHAR Dùng để xác định hàng cha của hàng con. Nếu không dùng các tính năng như sum tổng, 
Static TINYINT (1/0) Dùng để xác định các hàng có lấy dữ liệu từ bảng dữ liệu không

Đối với điều kiện cột hoặc viết trực tiếp điều kiện IF() trong các cột I1, I2, ... hoặc dùng ColumnConditions trong ReportFrame (có thể phối hợp với BeforeQuery)

RowCondition

Cách setup điều kiện RowCondition

Cột RowCondition hay còn gọi là điều kiện hàng, quyết định câu lệnh where cũng như 1 số thủ tục/phép toán trên bảng. Trước hết nó phải chuẩn đúng theo dạng JSON, nó xây dựng câu lệnh where giống với Mongoose (nhưng nên nhớ rằng bản chất của nó không phải là Mongoose). Cú pháp của nó thường là như sau:

{
  "<tên-cột>": {
    "$<toán-tử>": <giá-trị>
  },
  //các điều kiện khác
}

Thí dụ chẳng hạn: để xây câu lệnh WHERE Age >= 18 AND Country = "USA", Ta viết như sau:

{
   "Age": {
     "$gte": 18
   },
   "Country": {
     "$eq": "USA"
   }
}

Trong trường hợp của dấu '=', ta có thể bỏ "$eq" đi, do đó, câu lệnh trên có thể viết gọn thành

{
   "Age": {
     "$gte": 18
   },
   "Country": "USA"
}

Nhiều điều kiện dùng chung 1 cột có thể bỏ chung 1 chỗ, chẳng hạn câu lệnh WHERE Age BETWEEN 18 AND 26 Có thể viết thành

{
   "Age": {
     "$gte": 18,
     "$lte": 26
   }
}

Lưu ý: Các <tên-cột> đều lấy từ bảng dữ liệu, hãy rất cẩn thận khi viết tên cột từ bảng dữ liệu sang!

Hiện tại, chúng ta đang hỗ trợ các phép toán tử sau:

Toán tử Yêu cầu giá trị VD Câu lệnh sinh ra Note
$eq Số/Xâu WHERE AGE = val
$gte Số/Xâu WHERE AGE >= val
$lte Số/Xâu WHERE AGE <= val
$lt Số/Xâu WHERE AGE < val
$gt Số/Xâu WHERE AGE > val
$ne Số/Xâu WHERE AGE != val
$in Mảng giá trị WHERE AGE IN (1, 2, 3, ...)
$nin Mảng giá trị WHERE AGE NOT IN (1, 2, 3, ...)
$like Xâu WHERE AGE LIKE '%cond%'
$regex Xâu WHERE AGE REGEXP 'cond'
$exists Số (1/0) WHERE AGE IS NOT NULL Nếu giá trị = 1 thì yêu cầu cột khác null, 0 thì ngược lại 
$lein3 Mảng giá trị WHERE LEFT(AGE, 3) IN (1, 2, 3, ..) Bởi nhiều truy vấn yêu cầu tài khoản/khoản mục kiểm tra bắt đầu với abcxyz, các câu lệnh ở dưới đây được viết ngắn trc
$lein4 Mảng giá trị WHERE LEFT(AGE, 4) IN (1, 2, 3, ..)
$nlein3 Mảng giá trị WHERE LEFT(AGE, 3) NOT IN (1, 2, 3, ..)
$nlein4 Mảng giá trị WHERE LEFT(AGE, 4) NOT IN (1, 2, 3, ..)

Để có thể thêm các phép toán logic như OR hoặc NOT, Cú pháp của nó như sau:

{
  "$or": {
    //các điều kiện 1 (cond-1)
  },
  "$not": {
    //các điều kiện 2 (cond-2)
  },
  "$and": {
    //các điều kiện 3 (cond-3)
  }
}

Mặc định nếu không viết gì, thì điều kiện sẽ là AND, như vậy, câu lệnh trên sẽ được viết như sau:

WHERE (<cond-1> OR <cond-1> OR ...) AND NOT (<cond-2> ... ) AND (<cond-3> AND ....)

Trong trường hợp cần nest các điều AND và OR vào với nhau, ta dùng

{
  "$or": [
    {"$and": {<cond-1>}},
    {"$and": {<cond-1>}},
  ],
}

//tương tự như vậy
{
  "$and": [
    {"$or": {<cond-1>}},
    {"$or": {<cond-1>}},
  ],
}

Một ví dụ tổng thể tổ hợp của những gì ở trên:

{
    "$or": {
        "ECNo": {
            "$like": "2005101%"
        },
        "$and": {
            "ECNo": "2005207",
            "ECVal": {
                "$gte": 1,
                "$lte": 6
            }
        }
    },
    "AccountNo": {
        "$like": "632%"
    }
}

Câu lệnh WHERE mà nó sinh ra là:

WHERE 
( 
  `ECNo` LIKE '2005101%' 
   OR 
   (`ECNo` = 2005207 AND `ECVal` >= 1 AND `ECVal` <= 6)
) 
AND `AccountNo` LIKE '632%'

Để thêm điều kiện kết hợp hoặc xử lý dữ liệu sau điều kiện WHERE: GROUP BY, HAVING, ORDER BY, LIMIT, ta dùng điều kiện tương tự ở trên, nhưng không thêm dấu $ trước đấy

Format Yêu cầu giá trị Ví dụ
"groupBy": [<cột 1>, ....] Dữ liệu dạng mảng "groupBy": ["ExpenseID"]
"orderBy": [<cột 1>, ....] Dữ liệu dạng mảng "orderBy": ["ExpenseID"]
"having": "<điều kiện>" Dạng xâu trong điều kiện SQL "having": "I1 != 0"
"limit": <number> Số nguyên dương "limit": 10
Các tính năng đặc biệt của RowCondition

1. $cascade và $>cascade

Trước hết, ReportFrame có hỗ trợ truyền điều kiện hàng cha xuống hàng con, để lấy ví dụ, xem đặc tả sau:

image.png

Theo quan sát: Các hàng dấu '+' đều yêu cầu: 
- PS Nợ 421x (tức PostType = 1 AND AccountNo LIKE '421%')
- Yêu cầu nó phải thuộc 1 trong 5 trong 5 năm ngày trên (tức PostDate IN ('YYYY-01-01',....))
ReportFrame hỗ trợ 2 cách truyền điều kiện từ cha đến con: $cascade và $>cascade, cú pháp của nó trong RowCondition như sau:

{
  "$cascade": {
    //các điều kiện truyền xuống  
  },
  //các điều kiện khác trong hàng
}

Lấy ví dụ trên, 

Sự khác biệt đơn giản giữa $cascade và $>cascade là sự trao đổi giữa sức mạnh và tốc độ giữa 2 cái: $cascade có thể điều kiện từ cha xuống cháu, trong khi $>cascade chỉ có thể điều kiện xuống con trực tiếp của nó:

image.png

Một vấn đề về tốc độ nghiễm nhiên xảy ra là khi $cascade sẽ lâu hơn $>cascade do phải xét từ đến cháu, hơn nữa $cascade truyền cho toàn bộ cây báo cáo nên phải cẩn thận khi gán ItemID và ReportID khi truyền điều kiện:

Note: $cascade và $>cascade hiện chưa mix được với nhau, nếu cả 2 cái cùng ở trong 1 RowCondition thì ngẫu nhiên cái nào đi trước sẽ được xét trước. Hơn nữa, cả 2 đều chỉ truyền điều kiện xuống cho con/cháu của nó chứ không phải chính bản thân nó. Do đó nếu như có quan hệ cha/con mà cả cha và con đều chung điều kiện X thì vẫn phải viết X trong cả $cascade và điều kiện ở hàng cha.

2. $eval và $evalAll

Để việc tính toán giữa các hàng dễ dàng và thuận tiện hơn RowCondition hỗ trợ thêm $evalAll, cú pháp của nó như sau:

{
  "$eval": "<công-thức-sql>"
}

Trong đó, các hàng được viết là #<giá-trị-ItemID>

Trong đó, <công-thức-sql> là biểu thức sql mà trong đó các hàng khác được đánh dấu bằng dấu # kết hợp với các phép toán SQL thông thường. Các hàng được đánh dấu theo ItemID sẽ được truyền vào công thức để tính tất cả giá trị các cột I (và DynCol_ nếu như dùng động cột).

Thí dụ, giả sử ta có hàng ItemID = I.4 có RowCondition được viết như sau:

{
  "$evalAll": "#I.1 - #I.2 + #I.3"
}

Thì tất cả các cột I1, I2, ... trong hàng I.4 đó sẽ tính tương ứng với công thức lấy hàng I.1 trừ hàng I.2 rồi cộng I.3,

Một bất lợi khá là rõ ràng đó là $evalAll không thể lấy các ItemID động, do đó với các trường hợp như vậy các lập trình viên vẫn cần phải tự viết code để cập nhật các giá trị.

Lưu ý: tính năng chưa test nhiều (khi sử dụng, hãy báo lại người chịu trách nhiệm bảo trì để sửa lỗi gặp phải)

$eval: ngược lại tính công thức theo ô thay vì theo hàng, công thức của nó tương tự như trên, nhưng thêm 1 điều kiện đó là ItemID không được phép có dấu '.' trong đó. Các ô được viết theo công thức #<tên-hàng>.<tên cột> và công thức phải thuộc dạng:

{
  "$eval": "<tên-cột-1> = <công-thức-sql-1>, <tên-cột-2> = <công-thức-sql-2>, ..."
}

Lấy ví dụ chẳng hạn

{
  "$eval": "I1 = #I#01.I1 - #I#02.I2 + #I#03.I3"
}

Đồng thời một lưu ý đo là: $eval và $evalAll không dùng chung được trong cùng 1 hàng (bởi không có thứ tự rõ ràng trong JSON)

3. Ít nhất: $>0

Đối với các hàng cần hiện ít nhất 1 hàng kể cả có dữ liệu hay không ta dùng "$>0": 1. Điều này đảm bảo luôn có 1 hàng dữ liệu trong các trường hợp:

  • Sử dụng động cột
  • Hàng không dùng hàm aggerate nhưng đặc tả vẫn yêu cầu hiện ít nhất 1 lên (rất hiếm gặp)
{
  "$>0": 1
}

 




Back to top