Page History

Use this function to create a collection of entity or attribute instances (meeting certain criteria).

Syntax

 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.

• COLLECT Child.name FROM ALL Child results in a collection of the values “Kim” =  [ “Kim”, “Rick”, “Bob” and “Mary” ]
• COLLECT Child FROM Teacher[Teacher_2].teaches_Children results in a collection of the instances   = [ Child_1, Child_3 and Child_4 ]
• COLLECT Child.hobbies FROM Teacher[Teacher_1].teaches_Children results in a collection of the values “Reading” =  [ “Reading”, “Dancing”, “Tennis”, “Painting” and “Basketball”“Basketball” ]
• COLLECT Child.name FROM ALL Child WHERE ( Child.hobbies = “Reading” ) results in a collection of the values “Kim” and “Bob” = [ “Kim” and “Bob” ]
• COLLECT Child.hobbies FROM ALL Child WHERE ( Child.name = “Mary” ) results in a collection of the value “Football” = [ “Football” ]

Use this version of the COLLECT FROM [WHERE] function for complex nested selections in which it is necessary to use an alias.

Syntax

 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:

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:

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:

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:

COLLECT Child.Name 
FROM (
  COLLECT Person.hasChildren 
  FROM ALL Person 
  WHERE ( Person.hasChildren != ? AND Person.Age > 40 ) ) NAMED Child  

This expression results in a collection with only the name “Kim”.

You can make it as complex as you like, for example:

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”.

thisPerson := Person ;
 
COLLECT Person
    FROM ALL Person 
    WHERE ( Person != thisPerson )

Use this function to extract the value from a single valued collection or list.

Syntax

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  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”