Nested For Each commands to implement a Join

Unofficial Content
This documentation is valid for:

This article concerns to retrieve for each record of a table, some records from another table: the related ones.

Let’s suppose the developer needs to list all the stored countries and, for each country, after printing its name, he/she needs to print all the customers data that belong to that country.

Observe the following transactions structures:

Country
{
   CountryId*
   CountryName
}

Customer
{
   CustomerId*
   CustomerName
   CustomerEmail
   CountryId
   CountryName
}

The behavior needed by the developer is achieved with a pair of nested For Each commands  where the first one must scan the COUNTRY table and the second one must scan the CUSTOMER table. Observe the following procedure source:

For each Country
   Print country {CountryName}
   For each Customer
       Print customer {CustomerName, CustomerEMail}
   Endfor
Endfor

Given this definition, GeneXus determines that the base tables of each For each are different and there is a 1-N kind of relation (which may be an indirect relation) among the scanned tables. That is to say, for each record of the main For Each base table, GeneXus finds N records directly or indirectly related to it in the nested For Each base table. 

On finding this relation, it will apply automatic filter conditions to the nested For Each so as to keep only these related records. In the example case, the first For Each base table is COUNTRY and the second For Each base table is CUSTOMER. As GeneXus finds an attribute common to the main For Each base table and the nested For Each base table, CountryId, it determines that this attribute will act as filter condition when scanning the nested table. The developer does not have to include a Where clause in the nested For Each to filter the customers of the main For Each country. Since they are related records, this condition is implicitly applied by GeneXus.

In runtime the list looks as follows: 

JoinResultShownInRuntime

Since no order clause was specified for any of the nested For Each commands, we can see that:

  • GeneXus selected to sort the COUNTRY table by its primary key (CountryId). This is the default behavior for an independent For Each with no order clause.
  • GeneXus did not select to sort the CUSTOMER table by its primary key (CustomerId), but by the relation attribute (CountryId) in order to optimize the query.

The developer may obviously specify certain desired orders and GeneXus will optimize as much as possible.

This is one of the most common cases of nested For Each commands, where we want to scan a table and, for each record of this table, to scan another table related to the first one with an N-1 relation. GeneXus finds this relation and it only retrieves the associated records. Hence the name of JOIN given to this case.


Another example: Several nested For Each commands

Now consider the following transaction structures (they are almost the same as above, except that the Continent transaction is new, and each country belongs to one continent):

Continent
{
    ContinentId*
    ContinentName
}

Country
{
    CountryId*
    CountryName
    ContinentId
    ContinentName
}

Customer
{
    CustomerId*
    CustomerName
    CustomerEmail
    CountryId
    CountryName
}

Suppose a list is required to show all the stored continents and, for each continent, after printing its name, the developer needs to print each country name that belongs to that continent, followed by all the customers data of that country.

The following procedure source shows the code that solves the developer requirement:

For each Continent
    Print continent {ContinentName}
    For each Country
        Print country {CountryName}
        For each Customer
            Print customer {CustomerName, CustomerEMail}
        Endfor
    Endfor
Endfor

The logic is the same as in the previous case:

GeneXus determines that the base tables of the nested For each commands are different and there is a 1-N of direct relation among the scanned tables. That is to say, for each record of the main For Each base table (CONTINENT), GeneXus finds N records directly related to it in the nested For Each base table (COUNTRY), and for each record of this nested For each base table, GeneXus finds N records directly related to it in the last nested For Each base table (CUSTOMER).

On finding this relation, it will apply automatic filter conditions to each nested For Each so as to keep only the related records in each case. In the example, GeneXus finds a common attribute between the main For Each base table (CONTINENT) and the immediately nested For Each base table (COUNTRY), ContinentId, so, it determines that this attribute will act as filter condition when scanning the COUNTRY table. Genexus also finds a common attribute between the nested For Each base table (COUNTRY) and the last nested For Each base table (CUSTOMER), CountryId, so, it determines that this attribute will act as filter condition when scanning the CUSTOMER table. 

In runtime the list looks as follows: 

ThreeNestedForEachCommandsShownInRuntime

The quantity of nested For Each commands could continue increasing without limit (in the above example, if appropriate, for each customer the developer might need to scan their N invoices and for each invoice, their N lines).
 

Another example: Nested For Each commands to scan indirectly related tables

If the developer needs to make a list with the customers issued by continent, he/she has another case of nested For Each commands with different base tables, where the information to be listed has the required relation. In this case, the developer needs to scan the CONTINENT and CUSTOMER tables. Note that, although they are not directly related, there is an indirect relation between them. In fact, CONTINENT belongs to the CUSTOMER extended table. Therefore, for each customer, only one continent is related to it.  For this reason, the developer does not need to specify a where clause in the internal For Each to filter the main For Each continent customers. 
This can be clearly seen in the navigation list, that will show the filter: “ContinentId = ContinentId” for the second For Each.

In runtime the list looks as follows: 

NestedForEachCommandsToScanIndirectRelatedTablesInRuntime
 

Videos

Start Video Simple For Each command and nested For Each command to list related info