Doubt regarding OQL query

0
Hello, I am new to OQL query. trying to understand the syntax of it. Got a OQL query to selects the name of the department and a count of shifts Can you please explain me each line of this code, I am not able to get the syntax.   Please help me out. Thanks SELECT D.Name DepartmentName, COUNT(S/id) Number FROM UserManager.Shift S JOIN S/UserManager.Shift_Department/UserManager.Department D LEFT OUTER JOIN S/UserManager.Shift_Employee/UserManager.Employee E WHERE E/ID = NULL GROUP BY D.Name
asked
1 answers
0

Hi Trishla,

 

Firstly, I would highly recommend taking a look at the OQL documentation available here: OQL | Mendix Documentation

 

OQL is in somewhat similar to SQL. Therefore, having some knowledge of SQL will be very helpful.

 

The SELECT clause determines what is returned. It is generally in the form [Table].[Column] [Alias]. It may also be a function such as COUNT([Table].[Column]) [Alias]. For the available functions, see the docs.

In your case, the following:

  • D.Name DepartmentName will return the Name column of your table with the alias OR name D and assign the alias DepartmentName to the returned column
  • COUNT(S/id) Number will count the number lines in the id column of your table with the alias OR name S and assign the alias Number to the returned column

The FROM and JOIN clauses determine the data you extracted. There are some comprehensive material available on SQL JOINS, so please look into that.

In your case, the following:

  • You start with the table Shift (also the entity name) from the UserManagement module and assign the alias S
  • You then join the table Department from the UserManagement module, using the Shift_Department association, and assign the alias D to the table (Note D now only refers to the columns associated with the Department table)
  • You then join the table Employee from the UserManagement module, using the Shift_Employee association, and assign the alias E to the table (Note E now only refers to the columns associated with the Employee table)

The WHERE clause helps to filter the values extracted from the FROM and JOIN clauses.

In your case, the following:

  • You filter the data to only contains rows where the ID column of the table with the alias OR name E is NULL (or empty/void)

The GROUP BY clause help group data based on a column (or columns!) and is required for certain functions, such as COUNT().

In your case, the following:

  • You group the data base on the Name column of the table with the alias OR name D

This results in a list, containing the unique Name values from the Department table and the count (number) of Shift associated with the Department Name where the Shift does not have a Employee associated with it.

answered