Documentation:Understanding OCL with reference to SQL

Understanding OCL with reference to SQL

This page was created by Alexandra on 2017-06-03. Last edited by Wikiadmin on 2026-07-29.

You can use this page to translate SQL query habits into object-oriented OCL thinking when you build searches and user interfaces in MDriven.

Start with objects, not result rows

SQL and OCL can both locate data, but they return and preserve different things.

In SQL, a query normally returns rows and columns. A row can contain values from several tables after a join. The result is data values; it is not a model object with known relationships.

OCL works with model objects and collections of model objects. When you evaluate Person.allInstances, the result is a collection of Person objects. Each returned object retains its type, attributes, and links to related objects.

For example, if Person has a Family association:

SQL-oriented question	OCL-oriented question
"Which columns should this query return?"	"Which object or collection of objects should be the result?"
Join tables to combine values into rows.	Navigate associations from an object, such as `person.Family`.
Use the query result as a disconnected set of row values.	Keep the returned `Person` objects, then follow their links or show their attributes in a ViewModel.

A class is stored in the SQL database as described in Documentation:SQL Database, but you normally write OCL against the model rather than against database tables.

Think in terms of a context

An OCL expression needs a context: an object or collection on which the expression operates. In application use, you usually already have that context. For example, a ViewModel may have a current Family object, from which it can navigate to related people.

Person.allInstances is a way to establish a starting collection when you need to find root objects:

Person.allInstances

This returns a collection of Person objects. It is comparable to starting with all rows in a person table, but its result is typed objects rather than database rows.

Once you have a collection, use dot navigation to obtain an attribute from every object in that collection:

Person.allInstances.FirstName

The result is a collection of first-name values. The dot means that the operation is applied to each object in the collection.

In most user-interface scenarios, do not start every expression with allInstances. Start from the object already relevant to the user interaction and navigate from it. This avoids treating each expression as an unrelated database query.

Translate common SQL patterns

The following examples use the model names Person, Family, FirstName, LastName, and Name used in the walkthroughs.

Find all objects

SQL shape	OCL shape	Result
`SELECT * FROM Person`	`Person.allInstances`	A collection of `Person` objects.

The SQL result may display similar values, but OCL retains the fact that each result is a Person. You can therefore continue with navigation such as person.Family.

Filter a collection

In SQL, a WHERE clause filters rows. In OCL, select filters objects in a collection.

Person.allInstances->select(p | p.FirstName = 'Peter')

Read this as:

Get all Person objects.
Name each object p while evaluating the condition.
Keep the objects where p.FirstName is 'Peter'.

The vertical bar (|) separates the iterator variable from the expression evaluated for each object. The iterator name is your choice; p makes the expression easier to read than an arbitrary name.

The result is still a collection of Person objects, including when only one person matches. It is not a row containing only a first name.

Navigate instead of joining

In SQL, filtering people by a family attribute generally requires a join between person and family tables. In OCL, navigate the association while evaluating the person:

Person.allInstances->select(p | p.Family.Name = 'Johnson - Anderson')

This expression returns the matching Person objects. p.Family.Name follows the Family link from each person and then reads the family's Name attribute.

You do not need to construct a joined row to ask about a linked object. The result remains persons, so each result can still be used as a person and can still navigate to its family.

Order objects

In SQL, you commonly use ORDER BY. In OCL, use orderBy on the collection:

Person.allInstances->orderBy(p | p.LastName, p.FirstName)

This returns an ordered collection of Person objects. The objects are ordered by last name and then first name; they are not converted to a two-column result row.

You can combine filtering and ordering:

Person.allInstances
  ->select(p | p.Family.Name = 'Johnson - Anderson')
  ->orderBy(p | p.LastName, p.FirstName)

Use OCL-PS to find database root objects

OCL-PS is the database-executed form of OCL used to find objects without first loading every candidate object into memory. Use it when you need to establish a root collection from persisted data, especially when the candidate set can be large.

For example, an in-memory evaluation of this expression starts from every person:

Person.allInstances->select(p | p.FirstName = 'Peter')

If there are many people, loading all of them and filtering afterward is inefficient. Run an equivalent suitable search as OCL-PS so that the expression is translated for database execution and only the matching object or objects are retrieved.

OCL-PS has an important limit: the expression must be convertible to SQL. Keep OCL-PS focused on finding the root objects. After the root is found, use OCL in the ViewModel to navigate from those objects and calculate what the interface needs.

Need	Use	Example outcome
Find persisted root objects that match search criteria	OCL-PS	Find the `Person` objects whose first name is Peter without loading all people first.
Navigate from an object or calculate values already in the application context	OCL	Read a person's family name through `person.Family.Name`.
Change data	EAL	Perform an action that updates an object. OCL and OCL-PS are query expressions and do not change data.

Let ViewModels shape the user interface

A SQL query often combines filtering, joins, and a selected list of attributes into one result set. In MDriven, separate those responsibilities:

Use OCL-PS, when needed, to find the root objects for a screen.
Use a ViewModel to define the attributes and related-object values shown to the user.
Use OCL in ViewModel columns and nesting definitions to navigate from the root object.

For example, if a list should show a person's first name, last name, and family name, first find the relevant Person objects. Then define ViewModel columns that read the person attributes and navigate to Family.Name. This keeps the screen based on real objects and their relationships rather than on a manually assembled SQL row.

Use a tuple only when you specifically need a collection of values assembled from attributes. A tuple is data held together for that expression; it does not preserve the original model object identity in the way a Person result does. For normal UI data extraction, use a ViewModel instead.

Practical checklist for SQL users

Before writing an expression, ask these questions:

What is the root object type I need: Person, Family, or another class?
Do I need to search persisted data for those roots? If yes, can the root search be expressed as OCL-PS?
Which association should I navigate instead of joining tables?
Should the expression return objects, or am I trying to construct a column-based display result?
If I need a display result, have I put the attribute selection in the ViewModel rather than in the root search?

For OCL syntax and operators, see Documentation:OCL Expressions and Documentation:Part 1 OCL Common Expressions. For information about direct SQL and the database representation, see Documentation:SQL and Documentation:SQL Database.

Walkthroughs

Watch Understanding OCL with reference to SQL, part 1 for the object-versus-row and context concepts. Watch part 2 for select, association navigation, ordering, and OCL-PS examples.

Contents