write-oql-queries

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.