Page History
Learn more about the collection functions EXISTS, EACH, ALL, COLLECT FROM WHERE, COLLECT FROM NAMED WHERE, COLLECT, UNPACK, LIST, SIZE, UNIQUE, SUBSET OF, UNION, INTERSECTION, DIFFERENCE, SYMMETRIC_DIFFERENCE.
Overview
Function | Description |
---|---|
EXISTS | Determines |
...
Table of Contents | ||
---|---|---|
|
EXISTS
...
whether an instance of a specified entity exists, optionally meeting certain criteria |
...
Syntax
Code Block |
---|
EXISTS instances [WHERE condition] |
...
. |
...
Return type
- boolean
Examples
Suppose the following data model.
...
EXISTS Person
= TRUEEXISTS Person WHERE (Person.age < 18)
= FALSEEXISTS Person WHERE (Person.gender = “m” AND Person.age > 35)
= TRUE
UI Text Box | ||
---|---|---|
| ||
The return value of a boolean can be |
Section | ||||||||
---|---|---|---|---|---|---|---|---|
|
EACH
EACH | Determines |
...
whether all instances of a specified entity meet a certain criteria |
...
Syntax
Code Block |
---|
EACH instances WHERE condition |
...
...
. |
...
Return type
- boolean
Examples
Suppose the following data model.
...
EACH Person WHERE (Person.age < 18)
= FALSEEACH Person WHERE (Person.age > 18)
= TRUEEACH Person WHERE (Person.age < 20)
= FALSEEACH Person WHERE (Person.gender = “m” OR Person.age > 35)
= FALSEEACH Person WHERE (Person.gender = “m” OR Person.age > 20)
= TRUE
ALL | Creates |
ALL
...
a collection of all instances of a specified entity. |
...
Syntax
Code Block |
---|
ALL entity |
- entity - Entity of which to collect all its instances.
Return type
- collection of entity instances
Examples
Suppose the following data model. The Parent
and Child
entities both have Person
as base entity.
...
...
ALL Parent
= [ Parent_1 , Parent_2 ]...
ALL Child
= [ Child_1 , Child_2 , Child_3 , Child_4 ]...
...
Creates a collection of entity or attribute instances (meeting certain criteria |
...
Syntax
Code Block |
---|
COLLECT entity | attribute FROM collection [WHERE expression] |
- entity or attribute - Entity or attribute to collect.
- collection - A collection of entity instances.
- expression - Boolean expression that represents the criterion the instance has to meet.
Return type
- collection of entity instances
- collection of attribute values
Examples
Suppose the following model. Entity Teacher
has a multivalued relation with entity Child
via the relation Teacher.teaches_Children
.
...
...
...
...
...
) |
...
. |
...
...
Version of |
...
COLLECT FROM [WHERE] |
...
for complex nested selections |
...
Syntax
Code Block |
---|
COLLECT entity | attribute FROM collection NAMED alias [WHERE expression] |
- entity or attribute - Entity or attribute to collect. This should contain the alias, e.g. alias.Name.
- collection - A collection of entity instances.
- alias - A name for the collection.
- expression - Boolean expression that represents the criterion the instance has to meet.
Return type
- collection of entity instances
- collection of attribute values
Examples
Suppose the following model with only instances of entity Person. Person.hasChildren is a relation from Person to Person.
...
To retrieve all the parent names, we use this expression:
Code Block |
---|
COLLECT Parent.name
FROM
COLLECT Person
FROM ALL Person
WHERE (Person.hasChildren != ?) NAMED Parent |
This expression results in “Rick”, “Julia” and “Joan”.
To retrieve all children names, we use this expression:
Code Block |
---|
COLLECT Child
FROM (
COLLECT Person.hasChildren
FROM ALL Person
WHERE (Person.hasChildren != ?)) NAMED Child |
This expression results in a collection of three instances of Person (i.e Person_1, Person_3 and Person_5, with names Kim, Bob and Sam).
To retrieve all children younger than 15, we use this expression:
Code Block |
---|
COLLECT Child
FROM (
COLLECT Person.hasChildren
FROM ALL Person
WHERE (Person.hasChildren != ?)) NAMED Child
WHERE (Child.Age < 15) |
This expression results in a collection of two instances of Person (i.e Person_3 and Person_5, with names Bob and Sam).
To retrieve the children’s names with a parent older than 40 years:
Code Block |
---|
COLLECT Child.Name
FROM (
COLLECT Person.hasChildren
FROM ALL Person
WHERE (Person.hasChildren != ? AND Person.Age > 40)) NAMED Child |
...
with |
...
You can make it as complex as you like, for example:
Code Block |
---|
COLLECT Child.Name
FROM (
COLLECT Parent.hasChildren
FROM (
COLLECT Person
FROM ALL Person
WHERE (Person.hasChildren != ?)) NAMED Parent
WHERE (Parent.Age > 40 )
) NAMED Child
WHERE (Child.Age < 18) |
This expression results also in a collection with only the name “Kim”.
...
type | note |
---|
...
an alias |
...
. |
...
Code Block |
---|
thisPerson := Person ;
COLLECT Person
FROM ALL Person
WHERE (Person != thisPerson) |
UNPACK | Extracts |
UNPACK
...
SIZE
This function determines the size of a collection.
Syntax
Code Block |
---|
SIZE ( collection ) |
- collection - A collection of attribute or entity instances. This can be an expression or a relation attribute for instance.
Return type
- integer
Example
Suppose you have a Parent
and a Child
entity, where Parent
has a multivalued relation with Child
via the relation Parent.has_Children
. With this model the following instances are created:
...
Then:
- if
Parent_1
is active,SIZE ( Parent.has_Children )
= 3 - if
Parent_2
is active,SIZE ( Parent.has_Children )
= 1 - without an active
Parent
instance,SIZE ( Parent.has_Children )
results in an error - if
Child_1
is active,SIZE ( Child.hobbies )
= 2 - if
Child_3
is active,SIZE ( Child.hobbies )
= 3 - if
Child_4
is active,SIZE ( Child.hobbies )
= 1 SIZE ( ? )
= 0
UI Text Box | ||
---|---|---|
| ||
COUNT and SIZE are similar except for UNKNOWN : COUNT ( ? ) = UNKNOWN |
UNIQUE
The UNIQUE
function filters duplicate items from a collection. An expression resulting in a collection, never contains duplicate values. A subexpression however, can contain duplicates.
Syntax
Code Block |
---|
UNIQUE ( collection ) |
- collection - A collection of attribute or entity instances.
Examples
Suppose the following model.
...
COLLECT Person.name FROM ALL Person
= "Kim”, “Rick”, “Bob” (a result never contains duplicate values)SIZE ( COLLECT Person.name FROM ALL Person )
= 4 (a subexpression can contain duplicate values)SIZE ( UNIQUE ( COLLECT Person.name FROM ALL Person ) )
= 3 (the collection holds three unique values)
SUBSET OF
...
the value from a single valued collection or list. |
...
Syntax
Code Block |
---|
UNPACK collection/list |
- collection/list - A collection or list of one entity or attribute instance.
Return type
- entity instance
- attribute value of any type
Examples
Suppose the following data model.
...
- UNPACK(COLLECT Person.name FROM ALL Person WHERE (Person.SequenceNumber = MIN(COLLECT Person.SequenceNumber FROM ALL Person))) results in “Ron”
- In case a second entry "Ron","490" exists, the expression "UNPACK(COLLECT Person.name FROM ALL Person WHERE (Person.SequenceNumber = MIN(COLLECT Person.SequenceNumber FROM ALL Person)))" will fail, because the UNPACK cannot resolve a list with two elements. To solve this the UNIQUE function has to be used: "UNPACK(UNIQUE(COLLECT Person.name FROM ALL Person WHERE (Person.SequenceNumber = MIN(COLLECT Person.SequenceNumber FROM ALL Person))))", which will result in "Ron".
- UNPACK(COLLECT Person.name FROM ALL Person WHERE (Person.SequenceNumber = MAX(COLLECT Person.SequenceNumber FROM ALL Person))) results in “Jenny”
Is the inverse of the LIST function. | |
LIST | Creates a list based on a value. Is the inverse of the UNPACK function. |
SIZE | Determines the size of a collection. |
UNIQUE | Filters duplicate items from a collection resulting from a subexpression in a larger expression. |
SUBSET OF | Returns |
TRUE if the items in a collection are all present in another |
...
Syntax
Code Block |
---|
collection1 SUBSET OF collection2 |
- collection1 - The collection that is tested to be a subset of the second collection.
- collection2 - The collection that is tested to hold all the items in the first collection.
Return type
- boolean
Examples
('a', 'b', 'c') SUBSET OF ('a', 'b', 'c', 'd')
= TRUE('a', 'b', 'c', 'd') SUBSET OF ('a', 'b', 'c')
= FALSE(Person.hobbies) SUBSET OF ([“Tennis”, “Soccer”, “Music”])
= TRUE ifPerson.hobbies = “Tennis”, “Soccer”
(Person.hobbies) SUBSET OF ([“Tennis”, “Soccer”, “Music”])
= UNKNOWNif Person.hobbies = ?
(Person.hobbies) SUBSET OF ([“Tennis”, “Soccer”, ?])
= FALSE ifPerson.hobbies = “Tennis”, “Soccer”
('a', 'b', 'c') SUBSET OF (['a', 'b', 'c'])
will result in an error.
...
type | note |
---|
...
collection |
...
. |
...
UNION | Adds two collections of the same base type to a |
...
Syntax
Code Block |
---|
UNION ( collection1 , collection2 ) |
...
...
new collection. |
...
Return type
- collection
...
Suppose you have a Parent
and a Child
entity, where Parent
has a multivalued relation with Child
via the relation Parent.has_Children
. With this model the following instances are created:
...
UNION ( Parent[Parent_1].has_Children , Parent[Parent_2].Has_Children )
results in a collection of allChild
instancesUNION ( Parent[Parent_1].has_Children.name , Parent[Parent_2].Has_Children.name )
= “Kim”, “Rick”, “Bob”, “Mary”- if
Child_2
is active,UNION ( Child.hobbies , “Reading” )
= “Tennis”, “Dancing”, “Reading” UNION ( Child[Child_1].hobbies , Child[Child_2].hobbies )
= “Reading”, “Dancing”, “Tennis”
INTERSECTION
This function determines the intersection of two collections. It returns a collection containing the items that are present in both specified collections.
Syntax
Code Block |
---|
INTERSECTION ( collection1 , collection2 ) |
- collection1, collection2 - Collections to be intersected. These collections must be of the same base type.
Return type
- collection
Examples
Suppose the following model. Entity Teacher
has a multivalued relation with entity Child
via the relation Teacher.teaches_Children
.
INTERSECTION | Determines the intersection of two collections. |
DIFFERENCE | Determines the difference between 2 collections. Returns a collection |
...
INTERSECTION ( Teacher[Teacher_1].teaches_Children , Teacher[Teacher_2].teaches_Children )
results in a collection ofChild
instancesChild_1
andChild_3
INTERSECTION ( Teacher[Teacher_1].teaches_Children.name , Teacher[Teacher_2].teaches_Children.name )
= “Kim”, “Bob”INTERSECTION ( Child[Child_1].hobbies , Child[Child_3].hobbies )
= “Reading”INTERSECTION ( Child[Child_2].hobbies , Child[Child_3].hobbies )
results in an empty list
DIFFERENCE
...
containing all the items from collection1 that are not present in |
...
Syntax
Code Block |
---|
DIFFERENCE ( collection1 , collection2 ) |
...
collection2 |
...
. |
...
Return type
- collection
Examples
...
...
...
DIFFERENCE ( [ "nv" , "bv" ] , [ "NV" ] )
= [ "bv" ]...
DIFFERENCE ( 1 , 1 )
results in an empty list...
Determines the symmetric difference between two collections. |
...
Returns a collection with the elements of the provided collections which are in either one of the collections, but not in both. |
...
Syntax
Code Block |
---|
SYMMETRIC_DIFFERENCE ( collection1 , collection2 ) |
- collection1, collection2 - Collections to be intersected. These collections must be of the same base type.
Return type
- collection
Examples
SYMMETRIC_DIFFERENCE ( [ "a", "b", "c"] , [ "c", "d", "e" ] )
= [ "a", "b", "d", "e" ]SYMMETRIC_DIFFERENCE ( [ "nv" , "bv" ] , [ "NV" ] )
= [ "bv" ]SYMMETRIC_DIFFERENCE ( 1 , 1 )
results in an empty list
A note on collections and duplicates.
Functions
Include Page | ||||
---|---|---|---|---|
|
Include Page | ||||
---|---|---|---|---|
|
Include Page | ||||
---|---|---|---|---|
|
Include Page | ||||
---|---|---|---|---|
|
Include Page | ||||
---|---|---|---|---|
|
Include Page | ||||
---|---|---|---|---|
|
Include Page | ||||
---|---|---|---|---|
|
Include Page | ||||
---|---|---|---|---|
|
Include Page | ||||
---|---|---|---|---|
|
Include Page | ||||
---|---|---|---|---|
|
Include Page | ||||
---|---|---|---|---|
|
Include Page | ||||
---|---|---|---|---|
|
Include Page | ||||
---|---|---|---|---|
|
Include Page | ||||
---|---|---|---|---|
|
Include Page | ||||
---|---|---|---|---|
|
Collections vs. lists
UNIQUE, SUBSET, UNION and INTERSECTION return a collection of data, i.e. double entries are removed. However, COLLECT statements, + and - return a list of data. You have to be aware of this when using the SIZE or UNPACK function.
Example
Statuses = ["a","b","c","d","a","b","c","d"].
Result
The expression "SIZE ( COLLECT Status FROM {Statuses} )" will result in 8. Note that duplicates are not removed.
The expression "SIZE ( UNIQUE ( COLLECT Status FROM {Statuses} ) )" will result in 4, as the unique filters the duplicates.
UI Text Box | ||
---|---|---|
| ||
When the expression is completely evaluated, all duplicates are filtered as well. Using COLLECT Status FROM {Statuses} as a default expression on an attribute, will result in 4 elements. |
...