Page History

...

COLLECT FROM [WHERE]

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

Syntax

 ALL entity

 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
WHERE ( Child.hobbies = "Reading" )

COLLECT FROM NAMED [WHERE]

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 )

UNPACK

Use this function to extract the value from a single valued collection or list. It is the inverse of the LIST function.

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 ) ) ) = “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 ) ) ) ) = "Ron".

• UNPACK ( COLLECT Person.name FROM ALL Person WHERE ( Person.SequenceNumber = MAX ( COLLECT Person.SequenceNumber FROM ALL Person ) ) ) = “Jenny”

LIST

Use this function to create a list based on a value. It is the inverse of the UNPACK function.

Syntax

LIST ( attribute/collection )

• attribute - An attribute of any data type.
• collection/list - A collection or list of values.

Return type

• a multivalued list with entries of any type

Examples

Suppose the following data model.

• LIST ( COLLECT Person.name FROM ALL Person ) = [ "Bob" , "Jane" , "Mary" , "Rick" , "Ron" , "Jenny" ]
  • In this example the LIST function adds no value, as the result of the COLLECT is already a list.
• LIST ( COLLECT Person.SequenceNumber FROM ALL Person ) = []
  • As Jenny has an unknown sequence number, the result of the COLLECT expression is ?. The LIST function creates an empty list in case the parameter has ? value.

This function determines the size of a collection.

Syntax

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:

UNIQUE

The UNIQUE function filters duplicate items from a collection. An expression resulting in a collection, never contains duplicate values. A subexpression with the COLLECT statement however, can contain duplicates. See the note on collections and duplicates for more info.

Syntax

 UNIQUE ( collection )

Return type

• collection

Examples

Suppose the following model.

SUBSET OF

This function returns TRUE if the items in a collection are all present in another collection.

Syntax

 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

Venn diagram

Image Removed

Examples

1 SUBSET OF Address.Numbers

Given Adress.Numbers = ?

UNION

Adds two collections of the same base type to a new collection.

Syntax

 UNION ( collection1 , collection2 )

• collection1 - First collection to be added to the new collection.
• collection2 - Second collection to be added to the new collection.

Return type

• collection

Venn diagram

Image Removed

Examples

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:

INTERSECTION

This function determines the intersection of two collections. It returns a collection containing the items that are present in both specified collections.

Syntax

INTERSECTION ( collection1 , collection2 )

• collection1, collection2 - Collections to be intersected. These collections must be of the same base type.

Return type

• collection

Venn diagram

Image Removed

Examples

Suppose the following model. Entity Teacher has a multivalued relation with entity Child via the relation Teacher.teaches_Children.

...

DIFFERENCE

This function returns a collection containing all the items from collection1 that are not present in collection2.

Syntax

DIFFERENCE ( collection1 , collection2 )

...

• collection1, collection2 - Collections to be compared. These collections must be of the same base type.

Return type

• collection

Venn diagram

Image Removed

Examples

...

...

SYMMETRIC_DIFFERENCE

This function determines the symmetric difference between two collections. It returns a collection with the elements of the provided collections which are in either one of the collections, but not in both.

Syntax

SYMMETRIC_DIFFERENCE ( collection1 , collection2 )

...

• collection1, collection2 - Collections to be compared. These collections must be of the same base type.

Return type

• collection

Venn diagram

Image Removed

Examples

...

...

A note on collections and duplicates

An expression resulting in a collection does not contain duplicates. Please be aware however, that intermediary results of a COLLECT statement can contain duplicates. You have to be aware of this when using the SIZE or UNPACK function, or when using TSL.

This is best illustrated with the following examples.

...

 TSL:

The ages present are: [[[ COLLECT Person.Age FROM ALL Person ]]].

...

Now an example with the UNPACK function. We leave out the first instance from the previous example.

...