# The iterator.ofEntity() Function
reviewed: 11 April 2025
The function iterator.ofEntity() is the CaseMaster equivalent of recordsets.
A Simple Example
The following example shows the use of the iterator.ofEntity() function to loop over data in the database.
function test()
iterate iterator.ofEntity(
<
<@iterator/entity
name: 'client'
entity: 'crm/client'
where: 'surname % "s"'
orderBy: 'name'
load: 'title,firstName,middleName,surName'
>
>
)
response.write( bo.attr( [client], '_fullName' ) )
response.write( '<br>' )
end-iterate
end-function
Let us look at the example step-by-step:
- The first (and most important and only mandatory) parameter to the function is a property bag of
<@iterator/entity>. Often, the name of the qualifier is left out but adding it will give the developer intellisense support. In the paragraph Joins we will explain how you can have multiple<@iterator/entity>entries - The iterator that the function creates can be used in the
iterate end-iterateconstruct so you can loop over the recordset - Inside the
iterate end-iterateyou can refer to the BO as specified by the tagnameof the<@iterator/entity> - The optional
wheretag is used to specify a where clause (the default is no where clause) - The optional
orderBytag is used to specify an order clause (the default is no particular order) - The optional
loadtag is used to specify the attribute group to load from the database (the default is the*group)
Note that the query is not executed until the iterator is used in an iterate end-iterate.
The Other Parameters
The iterator.ofEntity() has 5 parameters:
| Parameter | Usage | Default Value |
|---|---|---|
| Entities | The entity or entities that make up the query | No default |
| Rows | The maximum number of rows that will be returned | 0 (indicating no maximum) |
| Start | On what row to start (can be used for paging) | 0 (indicating first row) |
| Distinct | Should the query return only distinct rows | false() |
| Mode | The mode of the recordset (see paragraph Modes) | iteratorMode.Stream |
Using Rows and Start
You can use the start and rows parameters to implement paging or a 'top x' logic.
The rows parameter is used to specify the number of rows per page for lists. Read here for more detail.
Modes
Normally the default mode is correct and uses a good balance between performance, versatility and memory usage.
There are three modes and they differ on a number of fronts:
- Can the iterator be reused? (see also paragraph on Re-using Iterators)
- What is the memory usage of the iterator
- Is there a delay on first use?
| Mode | Usage | Pros / Cons |
|---|---|---|
iteratorMode.Collection |
When first used, a collection with BO's will be created | Reusable, higher memory usage, slower on first iteration |
iteratorMode.Stream |
A collection of BO's will be built up whilst iterating over the records for the first time | Reusable, higher memory usage, no delay on first iteration |
iteratorMode.StreamLowMemory |
No collection is build up, the system reads-and-forgets one record at the time | Non reusable, low memory usage, no delay on first iteration |
Distinct
The distinct parameter can be used to force a distinct query.
The following example loops over all the dates for which a visit exists.
Note that you would typically have a restricted load group when using the distinct option.
iterate iterator.ofEntity(
<
<
name: 'visit'
entity: 'event/visit'
where: '#is( "isActive" )'
load: 'date'
>
>
distinct: true()
)
// You can refer to bo.atgtr( [visit], 'date' ) in here
end-iterate
Re-using Iterators
There can be a significant performance gain when you re-use an iterator. Doi make sure to reset the iterator between the uses. Both the modes collection and stream will load the results in memory.
set(
'test',
iterator.ofEntity(
<
<@iterator/entity
name: 'client'
entity: 'client'
where: 'surname % "s"'
orderBy: 'name'
load: 'title,firstName,middleName,surName'
mode: iteratorMode.stream
>
>,
)
)
iterate [test]
response.write( bo.attr( [client], '_fullName' ) )
response.write( '<br>' )
end-iterate
iterator.reset( [test] )
iterate [test]
response.write( bo.attr( [client], '_fullName' ) )
response.write( '<br>' )
end-iterate
iterator.reset( [test] )
iterate [test]
response.write( bo.attr( [client], '_fullName' ) )
response.write( '<br>' )
end-iterate
Note that iterators of mode StreamLowMemory can also be reset but the query will be re-executed when you use it again.
Joins
We have seen that you can have multiple entries of <@iterator/entity> in the entities proeprty bag. This is how you can create join queries.
Imagine a data model where geoLocations can be linked to crmAccounts by means of the many-to-many table crmAccountLocation.
The following shows how to create a join between the 3 tables.
iterate iterator.ofEntity(
entities: <
<
name: 'account'
entity: 'crm/account'
orderBy: 'name'
where: 'status=1'
>
<
name: 'locationAccount'
entity: 'crm/accountLocation'
>
<
name: 'location'
entity: 'geo/location'
orderBy: 'postcode'
>
>
rows: 50
)
// Do stuff here
end-iterate
The join relies on foreign key relations between the entities. CaseMaster will try not to be too fuzzy about the sequence of the entries but you sometimes have to 'play' with the sequence if you have many entries, especially when multiple 'resolve paths' exist between the entities. See also the next paragraph on advanced features.
Referring to Entities in orderBy and where
In a join (where you thus have multiple entities) you can refer to 'the other entities' in the orderBy or where of any of the entities.
The following example shows how we can refer to the entity user in the orderBy tag of the entity group.
The - in front of the attribute means 'descending'. Similarly, a + would mean ascending and is the default.
iterate iterator.ofEntity(
<
<@iterator/entity
name: 'user'
entity: 'user'
>
<
name: 'member'
entity: 'user/group/member'
>
<
name: 'group'
entity: 'user/group'
orderBy: 'user:name,group:-name' // Refer to user in the orderBy for group
>
>
)
response.write( formatString( '{0} - {1} - {2}', bo.pk( [user] ), bo.pk( [member] ), bo.pk( [group] ) ) )
response.write( '<br>' )
end-iterate
Similarly, you can also refer to other entities in where clauses:
iterate iterator.ofEntity(
<
<@iterator/entity
name: 'user'
entity: 'user'
>
<
name: 'member'
entity: 'user/group/member'
>
<
name: 'group'
entity: 'user/group'
where: 'member:id>2 & group:id="devl"' // Refer to member in where clause of group
>
>
)
response.write( formatString( '{0} - {1} - {2}', bo.pk( [user] ), bo.pk( [member] ), bo.pk( [group] ) ) )
response.write( '<br>' )
end-iterate
ResolveFKGroup
Imagine a scenario where you loop over thousands of rows and inside the iterate end-iterate you want to do something with bo.attrFormatted() on a foreign key attribute.
iterate iterator.ofEntity(
entities: <
<
name: 'locationAccount'
entity: 'crm/accountLocation'
>
>
)
response.write( bo.attrFormatted( [locationAccount], 'location' ) )
response.write( '<br>' )
response.write( bo.attrFormatted( [locationAccount], 'account' ) )
response.write( '<br>' )
end-iterate
For every row, CaseMaster will have to make two additional database calls; one to retrieve the name of the location (or rather, the value of the label group) and one for the name of the account.
The following small change (adding the resolveFKGroup tag) will instruct CaseMaster to 'resolve' the foreign key attributes location and account. That is, it will create a more complex query so that it also joins the relevant tables and attributes in so that bo.attrFormatted( [locationAccount], 'account' ) and bo.attrFormatted( [locationAccount], 'account' ) can be resolved without any additional queries to the database.
iterate iterator.ofEntity(
entities: <
<
name: 'locationAccount'
entity: 'crm/accountLocation'
resolveFKGroup: 'location,account'
>
>
)
response.write( bo.attrFormatted( [locationAccount], 'location' ) )
response.write( '<br>' )
response.write( bo.attrFormatted( [locationAccount], 'account' ) )
response.write( '<br>' )
end-iterate
Directives and Aliases
Each <@iterator/entity>has two tags that are for advanced use: directive and alias. These are designed for joins and are typically used in the following scenarios:
- You are joining a number of entities where there are multiple 'resolve paths' between the entities
- You want to include the same entity twice
I will explain directives using the following datamodel (using standard CaseMaster tables):
| Entity | Usage | Relations |
|---|---|---|
user |
User profile | |
user/group |
User access groups | |
user/group/member |
Links users to groups | FK to user and FK to user/group |
user/login |
Login details for a user profile | One-to-one relation with user, id is FK to user |
The following code works fine without directives:
iterate iterator.ofEntity(
<
<@iterator/entity
name: 'user'
entity: 'user'
>
<
name: 'member'
entity: 'user/group/member'
>
<
name: 'group'
entity: 'user/group'
>
>
)
response.write( formatString( '{0} - {1} - {2}', bo.pk( [user] ), bo.pk( [member] ), bo.pk( [group] ) ) )
response.write( '<br>' )
end-iterate
And generates SQL likes the following:
SELECT *
FROM (
(
"userGroupMember" INNER JOIN "user" ON "user"."id" = "userGroupMember"."user"
)
INNER JOIN "userGroup" ON "userGroup"."id" = "userGroupMember"."userGroup"
)
Note how the system has singled out user/group/member as the anchor table and join user and user/group to it.
Now lets try to also show some information about the user/login:
iterate iterator.ofEntity(
<
<@iterator/entity
name: 'user'
entity: 'user'
>
<
name: 'member'
entity: 'user/group/member'
>
<
name: 'group'
entity: 'user/group'
>
<@iterator/entity
name: 'login',
entity: 'user/login'
load: 'status'
// directive: 'user:id'
>
>
)
response.write( formatString( '{0} - {1} - {2} - {3}', bo.pk( [user] ), bo.pk( [member] ), bo.pk( [group] ), bo.pk( [login] ) ) )
response.write( '<br>' )
end-iterate
No we get an error as the generated SQL is not valid.
The reason is that the system did not manage to join user/login in as well. This is because CaseMaster will, without a directive, not link to the same table twice. The solution is to use a directive and instruct CaseMaster to join user/login to user via its id attribute.
iterate iterator.ofEntity(
<
<@iterator/entity
name: 'user'
entity: 'user'
>
<
name: 'member'
entity: 'user/group/member'
>
<
name: 'group'
entity: 'user/group'
>
<@iterator/entity
name: 'login',
entity: 'user/login'
load: 'status'
directive: 'user:id'
>
>
)
response.write( formatString( '{0} - {1} - {2} - {3}', bo.pk( [user] ), bo.pk( [member] ), bo.pk( [group] ), bo.pk( [login] ) ) )
response.write( '<br>' )
end-iterate
The generated SQL is:
SELECT *
FROM (
(
(
"userGroupMember" INNER JOIN "user" ON "user"."id" = "userGroupMember"."user"
)
INNER JOIN "userGroup" ON "userGroup"."id" = "userGroupMember"."userGroup"
)
INNER JOIN "userLogin" ON "userLogin"."id" = "user"."id"
)
The alias is best explained using the example of an FX table that has a from and to foreign key, both to the CCY table.
This requires both aliases (at least one) and directives to work:
iterate iterator.ofEntity(
<
<@iterator/entity
name: 'fx'
entity: 'fx'
>
<
name: 'from'
entity: 'ccy'
directive: 'fx:from'
alias: 'from'
>
<
name: 'to'
entity: 'ccy'
directive: 'fx:to'
alias: 'to'
>
>
)
response.write( formatString( '{0} - {1} - {2}', bo.pk( [from] ), bo.pk( [to] ), bo.attr( [fx], 'price' ) ) )
response.write( '<br>' )
end-iterate
<End of document>