Write Oql Queries

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:

mdl 1;
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(*) — over a view entity (no ID), count a non-null column
• Never aggregate a literal (sum(1), count(1)): it fails on HSQLDB — count a column, or sum(cast(1 as Integer))
• 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

-- CORRECT over a view entity, which has no ID: count a non-null column
select count(v.Name) from Finance.TransactionSummary as v

❌ 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(*) (a view entity has no ID: count a non-null column)
• No literal aggregated (sum(1), count(1)) and no bare 1 as X column a consumer will add to — cast it (HSQLDB)
• With GROUP BY: every non-aggregated column equals a GROUP BY expression, and no aggregate reads a grouped column
• 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.