Skill: Write OQL Queries for Mendix VIEW Entities

Reference files

• reference/patterns.md — the recurring OQL shapes (aggregation, joins across associations, date bucketing, ranking, filtered counts) and a full worked query, to adapt rather than derive.

Purpose

Generate correct OQL (Object Query Language) queries for Mendix VIEW entities. This skill helps you create VIEW entities with proper OQL syntax that will execute successfully in Mendix runtime.

When to Use This Skill

• User asks to create a VIEW entity
• User requests help with OQL queries
• User wants to create analytics, reports, or aggregated data views
• User needs to join entities or create calculated fields
• You encounter OQL syntax errors when creating VIEW entities

Critical OQL Syntax Rules

0. VIEW Entity Best Practices (CRITICAL)

RULE 1: All SELECT columns MUST have explicit AS aliases

Every column in the SELECT clause must have an alias that matches the entity attribute name:

-- ❌ WRONG - Missing aliases
create view entity Finance.CashFlowProjection (
  ProjectionDate: datetime,
  ProjectedIncome: decimal,
  ProjectedExpense: decimal
) as (
  select
    fl.ForecastDate,              -- Missing AS alias
    fl.ProjectedIncome,           -- Missing AS alias
    fl.ProjectedExpense           -- Missing AS alias
  from Finance.ForecastLine as fl
);

-- ✅ CORRECT - All columns have explicit aliases
create view entity Finance.CashFlowProjection (
  ProjectionDate: datetime,
  ProjectedIncome: decimal,
  ProjectedExpense: decimal
) as (
  select
    fl.ForecastDate as ProjectionDate,
    fl.ProjectedIncome as ProjectedIncome,
    fl.ProjectedExpense as ProjectedExpense
  from Finance.ForecastLine as fl
);

RULE 2: ORDER BY requires a LIMIT — prefer letting the consuming query sort

ORDER BY alone is rejected (mxcli check → MDL030; mx check → CE0174). ORDER BY with a LIMIT is valid and builds clean — that is exactly how you express a top-N view. As a default, prefer no ORDER BY/LIMIT so the UI component or microflow can sort and paginate the same view differently; reach for ORDER BY … LIMIT only when the view is intrinsically a top-N.

-- ❌ WRONG - ORDER BY without LIMIT (MDL030 / CE0174)
create view entity Finance.TopCustomers (...) as (
  select c.Name as CustomerName, sum(o.Amount) as TotalSpent
  from Finance.Customer as c
  inner join Finance.Order_Customer/Finance.Order as o
  GROUP by c.Name
  ORDER by TotalSpent desc        -- needs a LIMIT
);

-- ✅ CORRECT (preferred) - let the consuming page/microflow sort
create view entity Finance.CustomerTotals (...) as (
  select c.Name as CustomerName, sum(o.Amount) as TotalSpent
  from Finance.Customer as c
  inner join Finance.Order_Customer/Finance.Order as o
  GROUP by c.Name
);

-- ✅ ALSO VALID - an intrinsic top-N view (ORDER BY paired with LIMIT)
create view entity Finance.TopCustomers (...) as (
  select c.Name as CustomerName, sum(o.Amount) as TotalSpent
  from Finance.Customer as c
  inner join Finance.Order_Customer/Finance.Order as o
  GROUP by c.Name
  ORDER by TotalSpent desc
  LIMIT 100
);

Why these rules matter:

• Explicit aliases: Required for proper OQL-to-entity attribute mapping in Mendix
• ORDER BY/LIMIT: Omitting both keeps the view flexible (each page/microflow sorts and paginates as it needs); when you do sort, ORDER BY must be paired with LIMIT (MDL030)

UNION / UNION ALL are supported in view-entity OQL and round-trip cleanly — use them to combine multiple row kinds in one view (e.g. category rows plus group subtotals). Column count and types must line up across branches; ORDER BY (with its LIMIT) applies to the whole unioned result, not a single branch.

create or modify view entity Ledger.CategoryAndSubtotals (
  Label: string(100), Amount: decimal
) as (
  select c.Name as Label, sum(t.Amount) as Amount
  from Ledger.Category as c
  left join Ledger.Transaction_Category/Ledger.Transaction as t
  group by c.Name
  union all
  select 'TOTAL' as Label, sum(t.Amount) as Amount
  from Ledger.Transaction as t
);

1. Aggregate Functions (MUST BE LOWERCASE)

-- ❌ WRONG - Uppercase will fail
sum(o.Amount)
avg(o.Amount)
max(o.OrderDate)
min(o.Amount)

-- ✅ CORRECT - Lowercase
sum(o.Amount)
avg(o.Amount)
max(o.OrderDate)
min(o.Amount)

2. COUNT Function

-- ❌ WRONG - count(*) not supported in Mendix OQL
count(*)

-- ✅ CORRECT - Count by ID or entity
count(t.ID)           -- Count by ID attribute
count(t)              -- Count entity instances

Aggregate Function Return Types

Function	Input Type	Returns	MDL Declaration
count(expr)	any	Integer	attr: integer
sum(expr)	Integer	Integer	attr: integer
sum(expr)	Decimal	Decimal	attr: decimal
avg(expr)	any numeric	Decimal	attr: decimal
max(expr) / min(expr)	Integer	Integer	attr: integer
max(expr) / min(expr)	Decimal	Decimal	attr: decimal
max(expr) / min(expr)	DateTime	DateTime	attr: datetime
datepart(part, expr)	DateTime	Integer	attr: integer
length(expr)	String	Integer	attr: integer

Key rule: count() and avg() have fixed return types. sum(), min(), max() preserve the input type.

3. DATEPART Function (Comma Syntax)

-- ✅ CORRECT - Use comma syntax
datepart(YEAR, t.TransactionDate)
datepart(MONTH, t.TransactionDate)
datepart(QUARTER, t.TransactionDate)
datepart(WEEK, t.TransactionDate)
datepart(DAY, t.TransactionDate)

-- ❌ WRONG - FROM syntax not supported
DATEPART(YEAR from t.TransactionDate)

4. Enumeration Comparisons (String Literals)

-- ❌ WRONG - Qualified enum names
t.TransactionType = Finance.TransactionType.INCOME
t.Status != Finance.TransactionStatus.VOID

-- ✅ CORRECT - Use string literals
t.TransactionType = 'INCOME'
t.Status != 'VOID'

5. Division Operator (Colon, not Slash)

-- ❌ WRONG - Using / causes parsing errors
select amount / quantity as price
select (total - discount) * 100.0 / total as percentage

-- ✅ CORRECT - Use : for division
select amount : quantity as price
select (total - discount) * 100.0 : total as percentage

6. ORDER BY with Aliases

-- ❌ WRONG - Using expressions in ORDER BY
ORDER by datepart(YEAR, t.TransactionDate) desc

-- ✅ CORRECT - Use column aliases
select
  datepart(YEAR, t.TransactionDate) as OrderYear
from Finance.Transaction as t
ORDER by OrderYear desc

6b. ORDER BY DESC on a nullable column puts the nulls FIRST

ORDER BY <attribute> DESC does not give you the newest rows when some rows leave that attribute empty. Mendix emits the ordering with no null placement, so the database default applies — on PostgreSQL, DESC means NULLS FIRST.

-- ❌ MISLEADING - the empty rows come back first, so a top-N is not the top N
select g.Label as Label from Sudoku.Game as g
order by g.DealtAt desc
limit 5

-- ✅ CORRECT when the attribute is optional
select g.Label as Label from Sudoku.Game as g
order by g.DealtAt desc nulls last
limit 5

This is worth knowing because it does not look like a null problem. The result is stable across runs, so it reads as "the ordering is being ignored" rather than "the sort key is empty for some rows" — and the natural next step, falling back to order by id desc, answers a different question (insertion order, which only matches recency when nothing backdates a row).

Check before concluding anything: select count(*) as n from Sudoku.Game where DealtAt = empty.

Measured on Mendix 11.13.0 / PostgreSQL: with values present, ORDER BY on a DateTime is emitted to the database correctly and orders correctly. Null placement is database-specific (SQL Server and Oracle differ), so being explicit is also the portable choice.

7. Operators (Use != not <>)

-- ❌ WRONG - <> causes errors in Mendix
where t.Status <> 'VOIDED'

-- ✅ CORRECT - Use !=
where t.Status != 'VOIDED'

Note: Both != and <> are valid in standard SQL, but Mendix OQL only accepts !=.

8. IN Expression Syntax

-- ✅ IN with value list
where t.Status in ('ACTIVE', 'PENDING', 'REVIEW')

-- ✅ IN with subquery
where t.CustomerId in (
  select c.CustomerId from Shop.Customer as c where c.IsVIP = true
)

-- ✅ Enumeration values use identifiers, not captions
where t.Priority in ('HIGH', 'CRITICAL')  -- Not 'High', 'Critical'

9. Subqueries (Scalar and Correlated)

-- ✅ Scalar subquery in SELECT (returns single value)
select
  p.Name as ProductName,
  p.Price - (select avg(p2.Price) from Shop.Product as p2) as DiffFromAvg
from Shop.Product as p

-- ✅ Scalar subquery in WHERE
where p.Price > (select avg(p2.Price) from Shop.Product as p2)

-- ✅ Correlated subquery (references outer query by attribute)
select
  o.OrderNumber as OrderNumber,
  (select count(o2.OrderId) from Shop.Order as o2 where o2.CustomerId = o.CustomerId) as CustomerOrderCount
from Shop.Order as o

-- ✅ Correlated subquery via association (compare to .ID)
select
  p.Name as ProductName,
  (select pr.PriceInEuro from Shop.Price as pr
   where pr/Shop.Price_Product = p.ID
   ORDER by pr.StartDate desc limit 1) as LatestPrice
from Shop.Product as p

-- ❌ WRONG - bare alias without .ID
where pr/Shop.Price_Product = p    -- Doesn't resolve

-- ✅ CORRECT - compare to entity .ID
where pr/Shop.Price_Product = p.ID

10. Association Path Syntax

-- Association paths in OQL use '/' not '.'
-- ✅ CORRECT - slash prefix for association traversal
where l/Library.Loan_Member = m.ID
join l/Library.Loan_Book/Library.Book as b

-- ❌ WRONG - dot instead of slash
where l.Library.Loan_Member = m.ID     -- Error: does not resolve

11. JOIN Syntax (Association Traversal and ON Clause)

Mendix OQL supports both association traversal and SQL-style JOIN ON:

-- ✅ Association traversal (uses Mendix association path)
from Shop.Order as o
inner join o/Shop.Order_Customer/Shop.Customer as c

-- ✅ JOIN ON clause (SQL-style, for any condition)
from Shop.Order as o
inner join Shop.Customer as c on o.CustomerId = c.CustomerId

-- ✅ LEFT OUTER JOIN with ON clause
from Shop.Product as p
left outer join Shop.CompetitorProduct as cp on p.ProductCode = cp.ProductCode

When to use each approach:

• Association traversal (alias/Module.Association/entity): When joining on a Mendix-defined association
• JOIN ON (join entity on condition): When joining on arbitrary conditions or non-association fields

Step-by-Step Process

Step 1: Define VIEW Entity Schema

Always include @Position annotation:

/**
 * View entity description
 *
 * @since 1.0.0
 */
@position(300, 500)
create view entity Module.ViewName (
  Attribute1: type,
  Attribute2: type,
  -- ... more attributes
) as (
  -- OQL query goes here
);

Step 1b: Know the two clause orders

Mendix OQL accepts the select list in either position, and mxcli reads both:

-- Select-first. Write new views this way; the rest of this skill assumes it.
select c.Name as Name, count(o.ID) as Orders
from Shop.Customer as c
group by c.Name

-- From-first. Same query. This is what STUDIO PRO STORES, so it is what
-- `describe entity` gives you back — copy it, edit it, exec it unchanged.
from Shop.Customer as c
group by c.Name
select c.Name as Name, count(o.ID) as Orders

Note where group by sits: in the from-first order every clause except order by / limit comes before the select list, and the grammar enforces that. from … select … group by … is a parse error, not a variant.

Do not rewrite a described view into select-first just to make it look familiar — the stored text is what MxBuild validates against, and a needless rewrite is a diff for nothing.

Step 1c: Selecting an id makes an ASSOCIATION, not an attribute

Selecting a persistent entity's ID under an alias gives the view entity an association to that entity. The alias becomes the association's name, and the column is not one of the view entity's attributes — so do not declare one for it:

create view entity Sales.OrdersVE (
  order_date: DateTime              -- one attribute…
) as (
  from Sales."Order" as o
  select o.ID        as persistent_order   -- …but two columns
       , o.OrderDate as order_date
);

mxcli creates the association member from that column. There is no separate statement for it, and create association with a view entity at either end is refused — Mendix rejects it (CE6771), because the association needs an OqlViewAssociationSource that a plain one does not have.

Two rules:

• The alias must be free in the module, case-insensitively. It is the association's name, and Mendix reports "Duplicate name 'Meter' in module 'Trends'. Entities, associations and enumerations cannot share names." So as meter beside an entity called Meter fails — name it MeterRef.
• Reach the target through a join if it is not the FROM entity, and select the id off that alias: join r/Trends.Reading_Meter/Trends.Meter as m … select m.ID as MeterRef.
• Do not declare the id column as an attribute. MeterRef: Trends.Meter (or Trends.Meter.ID) in the attribute list parses — a bare qualified name is how MDL spells an enumeration type — and would be stored as an enumeration naming an entity: CE1613 at build, or mx check failing to load the project. mxcli refuses it (MDL080). The attribute list holds only the non-id columns.

Consider the flat alternative first. An association costs a second query at runtime — the view returns the foreign key, and the client then fetches the referenced objects in a batched IN (...) per page, materialising real objects in its state. Selecting a string copy instead is one statement, one join, no second retrieve, and the id is still there to look the object up with:

select cast(m.ID as string) as MeterId, m.MeterCode as MeterCode, …

Use the association when you want to bind widgets over it (MeterRef/MeterCode); use the cast when you just need the value.

Step 2: Write SELECT Clause

• Use lowercase aggregate functions: sum(), avg(), count()
• Use count(entity.ID) not count(*)
• Create meaningful aliases for all columns
• Use : for division operations

Step 3: Write FROM Clause

• Use table aliases (AS t, AS c, etc.)
• Navigate associations: Entity_Association/TargetEntity

Step 4: Add JOINs if Needed

-- Association join syntax
inner join Shop.Order_Customer/Shop.Customer as c
left join Shop.Product_Category/Shop.Category as cat

Step 5: Add WHERE Clause

• Use string literals for enum comparisons: 'value'
• Use standard comparison operators: =, !=, >, <, >=, <=

Step 6: Add GROUP BY if Using Aggregates

• Include all non-aggregated columns
• Use same expressions as SELECT (e.g., datepart())

Step 7: Verify Aliases

• Ensure ALL SELECT columns have explicit AS aliases
• Aliases must match entity attribute names exactly

Step 8: Validate Before Executing

mxcli check view.mdl -p app.mpr --references

This catches type mismatches (e.g., declaring long for a count() column that returns integer), missing module references, and OQL syntax errors — before they become MxBuild errors like CE6770 ("View Entity is out of sync with the OQL Query").

Step 9: Final Check

• Prefer no ORDER BY/LIMIT/OFFSET — let the UI component or microflow sort and paginate
• If the view is intrinsically a top-N, ORDER BY is allowed only when paired with a LIMIT (MDL030 / CE0174)

Common Mistakes to Avoid

❌ Mistake 1: Uppercase Aggregates

-- WRONG
select sum(amount) from ...

-- CORRECT
select sum(amount) from ...

❌ Mistake 2: Using count(*)

-- WRONG
select count(*) from Finance.Transaction

-- CORRECT
select count(t.ID) from Finance.Transaction as t

❌ Mistake 3: Qualified Enum Names

-- WRONG
where t.Status = Finance.Status.ACTIVE

-- CORRECT
where t.Status = 'ACTIVE'

❌ Mistake 4: Slash for Division

-- WRONG
select total / count as average

-- CORRECT
select total : count as average

❌ Mistake 5: Missing Column Aliases

-- WRONG
select
  fl.ForecastDate,
  fl.ProjectedIncome
from Finance.ForecastLine as fl

-- CORRECT
select
  fl.ForecastDate as ProjectionDate,
  fl.ProjectedIncome as ProjectedIncome
from Finance.ForecastLine as fl

❌ Mistake 6: Dot Instead of Slash for Association Paths

-- WRONG - dot notation for association
where l.Library.Loan_Member = m.ID

-- CORRECT - slash notation
where l/Library.Loan_Member = m.ID

❌ Mistake 7: Bare Alias in Association Comparison

-- WRONG - comparing association to bare entity alias
where pr/Shop.Price_Product = p

-- CORRECT - compare to entity .ID
where pr/Shop.Price_Product = p.ID

❌ Mistake 8: ORDER BY without a LIMIT in a VIEW

-- WRONG - ORDER BY alone (MDL030 / CE0174)
create view entity Finance.TopItems (...) as (
  select ...
  ORDER by Amount desc          -- needs a LIMIT
);

-- CORRECT (preferred) - let the UI sort/paginate
create view entity Finance.ItemTotals (...) as (
  select ...
  -- no ORDER BY / LIMIT
);

-- ALSO VALID - an intrinsic top-N view
create view entity Finance.TopItems (...) as (
  select ...
  ORDER by Amount desc
  LIMIT 100
);

Testing OQL Queries

Use mxcli oql to test queries against a running Mendix runtime (read-only preview mode):

# basic query (reads .docker/.env for connection settings)
mxcli oql -p app.mpr "select Name, Email from MyModule.Customer"

# json output for piping to jq
mxcli oql -p app.mpr --json "SELECT count(c.ID) FROM MyModule.Order AS c" | jq '.[0]'

# Explicit connection (no project file needed)
mxcli oql --host localhost --port 8090 --token 'AdminPassword1!' "SELECT 1"

# Test a view entity query before embedding it
mxcli oql -p app.mpr "select datepart(YEAR, o.OrderDate) as Year, sum(o.Total) as Revenue from Sales.Order as o GROUP by datepart(YEAR, o.OrderDate)"

The app must be running first: mxcli docker run -p app.mpr --wait

Troubleshooting: If you get "Action not found: preview_execute_oql", the Docker stack needs the -Dmendix.live-preview=enabled JVM flag. Re-initialize with: mxcli docker init -p app.mpr --force, then restart with mxcli docker run -p app.mpr --wait.

Workflow: OQL → VIEW ENTITY

1. Write and test interactively: mxcli oql -p app.mpr "select ..."
2. Iterate until the query returns expected results
3. Embed in a VIEW ENTITY with matching column aliases and attribute types
4. Validate before executing: mxcli check view.mdl -p app.mpr --references to catch type mismatches (e.g., long vs integer for count())
5. Apply and rebuild: mxcli exec view.mdl -p app.mpr && mxcli docker run -p app.mpr --fresh --wait

Integration with MDL Linter

The MDL linter checks for common OQL issues:

Rule: consistency/oql-syntax

• Validates VIEW entity OQL queries
• Checks for ORDER BY without LIMIT/OFFSET (CE0174)
• Checks for missing/empty SELECT or FROM clauses

How to Fix Linter Errors:

# lint a file
mendix> lint file 'path/to/file.mdl';

# Common error: ORDER by without limit
# error: view entity X: ORDER by requires limit or OFFSET. Studio Pro error: CE0174
# Fix: add limit clause

References

• Mendix OQL Documentation
• OQL Expressions
• OQL Functions
• Internal: packages/mendix-repl/docs/syntax-proposals/OQL_SYNTAX_GUIDE.md
• Internal: packages/mendix-repl/examples/VIEW_ENTITY_VALIDATION.md

Summary Checklist

When writing OQL queries for VIEW entities, always verify:

• CRITICAL: Entity has @Position annotation (e.g., @Position(300, 500))
• CRITICAL: All SELECT columns have explicit AS aliases matching entity attributes
• ORDER BY omitted so the UI sorts — or, for a top-N view, ORDER BY paired with a LIMIT (MDL030 rejects ORDER BY without LIMIT)
• Aggregate functions are lowercase (sum, avg, count, max, min)
• Using count(entity.ID) not count(*)
• DATEPART uses comma syntax: datepart(YEAR, field)
• Enum comparisons use enumeration identifiers, not captions: 'HIGH' not 'High'
• IN expressions use correct syntax: in ('VAL1', 'VAL2') or in (select ...)
• Division uses colon: amount : quantity
• Inequality uses != not <>
• All non-aggregated columns are in GROUP BY
• Association paths use / not .: alias/Module.Assoc not alias.Module.Assoc
• Association comparisons use .ID: pr/Shop.Price_Product = p.ID not = p
• Association navigation uses correct syntax: Entity_Assoc/Target as alias
• JOIN ON clauses use comparison operators: on a.Field = b.Field
• Subqueries are enclosed in parentheses and return appropriate values
• Validate before executing: Run mxcli check script.mdl -p app.mpr --references to catch type mismatches

Following these rules ensures your OQL queries will parse and execute correctly in Mendix runtime.