In Part 2 of the OQL Series, explore the power of OQL clauses and aggregation in Mendix. Learn to efficiently retrieve and summarize data using SELECT, WHERE, GROUP BY, and HAVING clauses, combined with aggregation functions like SUM, COUNT, and AVG. Unlock new ways to analyze and optimize your application data.

Dependencies: -
OQL

OQL Series Part 1: Introduction to Object Query Language in Mendix

IMPORTANT :- BookName , Price & AuthorName are the persistable entity attribute name please give the same name which are available in the entity, OQL_Series is the module name & by using a dot(.) you can select your entity.

Whenever you use “as” keyword(BookName as BookN) you have to make a Non persistable entity to return & please keep the NPE attribute name same as which you are using after “as” keyword(BookN).

Whenever you are working with association you have to use “as” keyword(BookName as BookN) & need to make NPE to return, without “as” keyword you are not able to write the OQL Query.

WHERE CLAUSE IN OQL:-

The WHERE clause is used to constraints data retrieval. You can use operators like AND, OR, >, <, ≥, and≤.

SELECT BookName as BookN from OQL_Series.Book where Price>=50

or

SELECT BookName from OQL_Series.Book where Price>=50

SELECT 
BookName as BookN,
Price as BookPrice
from OQL_Series.Book where Price<=100

SELECT 
BookName as BookN,
Price as BookPrice,
OQL_Series.Book/OQL_Series.Book_BookAuthor/OQL_Series.BookAuthor/AuthorName AS BookAuthorName
from OQL_Series.Book where Price>=50 and Price<=100

SELECT 
BookName as BookN,
Price as BookPrice,
OQL_Series.Book/OQL_Series.Book_BookAuthor/OQL_Series.BookAuthor/AuthorName AS BookAuthorName
from OQL_Series.Book where BookName='Mendix Lowcode 1'

SELECT 
BookName as BookN,
Price as BookPrice,
OQL_Series.Book/OQL_Series.Book_BookAuthor/OQL_Series.BookAuthor/AuthorName AS BookAuthorName
from OQL_Series.Book where BookName='Mendix Lowcode 1' or Price=20

ORDER BY CLAUSE:-

The ORDER BY clause is used to sort data in either ascending or descending order. By default, it sorts the data in ascending order.

SELECT 
BookName as BookN,
Price as BookPrice,
OQL_Series.Book/OQL_Series.Book_BookAuthor/OQL_Series.BookAuthor/AuthorName AS BookAuthorName
from OQL_Series.Book ORDER BY Price

SELECT 
BookName as BookN,
Price as BookPrice,
OQL_Series.Book/OQL_Series.Book_BookAuthor/OQL_Series.BookAuthor/AuthorName AS BookAuthorName
from OQL_Series.Book ORDER BY BookAuthorName DESC

LIMIT CLAUSE :-

The LIMIT clause is used to restrict the number of records returned by a query. It allows you to specify the maximum number of results to retrieve.

//It will give first 10 objects which are sorted by Price in ascending order.//



SELECT 
BookName as BookN,
Price as BookPrice,
OQL_Series.Book/OQL_Series.Book_BookAuthor/OQL_Series.BookAuthor/AuthorName AS BookAuthorName
from OQL_Series.Book ORDER BY Price LIMIT 10

//It will give all objects except 10 which are sorted by Price in descending order.//


SELECT 
BookName as BookN,
Price as BookPrice,
OQL_Series.Book/OQL_Series.Book_BookAuthor/OQL_Series.BookAuthor/AuthorName AS BookAuthorName
from OQL_Series.Book ORDER BY Price DESC OFFSET 10

//It will give 5 objects(11 to 15) except first 10 which are sorted by Price in ascending order.//


SELECT 
BookName as BookN,
Price as BookPrice,
OQL_Series.Book/OQL_Series.Book_BookAuthor/OQL_Series.BookAuthor/AuthorName AS BookAuthorName
from OQL_Series.Book ORDER BY Price LIMIT 5 OFFSET 10

AGGREGATION :-

// This will return the SUM of all book Prices


SELECT 
SUM(Price) as BookPrice
from OQL_Series.Book

SELECT 
AVG(Price) as BookPrice
from OQL_Series.Book where Price>=50 and Price<=100

SELECT 
MAX(Price) as BookPrice
from OQL_Series.Book

SELECT 
MIN(Price) as BookPrice
from OQL_Series.Book

SELECT 
COUNT(Price) as BookPrice
from OQL_Series.Book where Price>100

I hope this article has helped you understand OQL Clauses & Aggregation in Mendix.

Thanks for reading this! See you in the next blog post.