當今天資料超多,客戶意見也超多時,資料庫中的搜尋還在用之前提過的XPath就太慢惹! 我們要用更省力的方法同時從不同物件中抓出需要的資料,這時OQL就可以派上用場。
OQL 全名 Object Query Language,根據維基百科上的解釋OQL是一種物件導向資料庫的查詢語言(Object Query Language (OQL) is a query language standard for object-oriented databases modeled after SQL.),事實上,在我們輸入XPath後,他就會自動轉換為OQL,到SQL中進行查詢動作。
例如
輸入XPath:
//UserManager.Employee[Jobtitle = "Doctor”]
(Schema: Title, Firstname, Lastname, Jobtitle)
會被轉化為OQL:
SELECT Title, Firstname, Lastname, Jobtitle
FROM UserManager.Employee
WHERE Jobtitle = "Doctor"
到SQL裡變成:
SELECT UserManager$Employee.Title, UserManager$Employee.FirstName, UserManager$Employee.LastName, UserManager$Employee.Jobtitle
FROM UserManager$Employee
WHERE Jobtitle = "Doctor"
SELECT [ DISTINCT ] \
{ \
* \
| { entity_name | from_alias }.* \
| { expression [ [ AS ] column_alias ] } [ ,...n ] \
}
FROM
{
entity_name | ( sub_oql_query )
}
[ [ AS ] from_alias ]
{
{ INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } } JOIN
entity_path [ [ AS ] from_alias ]
ON <constraint>
} [ ,...n ]
WHERE <constraint>
首先,前往Mendix App Store 下載與載入OQL module
之後可以在Project Explorer > Project > App Store modules 看到下載的檔案
在module的檔案夾上按滑鼠右鍵,選擇Add Other > Data Set,寫入需要的OQL
例如:
SELECT D/Name DepartmentName, COUNT(S/id) Number
FROM UserManager.Shift S
JOIN S/UserManager.Shift_PlanningPeriod/UserManager.PlanningPeriod/UserManager.PlanningPeriod_Department/UserManager.Department D
LEFT OUTER JOIN S/UserManager.Shift_Employee/UserManager.Employee E
WHERE E/ID = NULL
GROUP BY D/Name
再來,創建一個可以執行OQL的Microflows,Activity可以在Toolbox裡找到
最後在Execute OQL上點左鍵兩下編輯內容,就可以使用了!