1

Refining a selection to use in a loop.

Is there an easy way to refine a selection that I have assigned to a variable? I'll explain my use case.

 

I'm having some performance issues when using select inside a loop.

The following example is causing me issues and crashing the whole database when I run it on several thousand records.

I am attempting to link all of the records in the table Revenue to a matching Store (N:1).

do as server

    let allRevenues := select 'Revenue';

    for revenue in allRevenues do

        let store := first(select 'Stores' where Name = revenue.'Store name');

        revenue.store := store;

    end

end

 

I believe this performance issue could be solved by selecting both tables outside of the loop and assigning them to variables, and then refining those selections inside the loop. What I would like to do is something like this:

 

do as server

    let allRevenues := select 'Revenue';

    let allStores := select 'Stores';

    for revenue in allRevenues do

        let store := first(select allStores where Name = revenue.'Store name');

        revenue.store := store;

    end

end

 

However, allStores is not a table, so I cannot select from it.

I couldn't find anything in the documentation about making a selection from an array of records that has been assigned to a variable. Is this possible?

4 replies

null
    • Wethrift
    • Nick_Drewe
    • 3 yrs ago
    • Reported - view

    I think i've solved this using the other select syntax. 

    For anyone having a similar problem, the (select variableName)[fieldName = value] syntax works for making a sub-selection from a variable.

    do as server
        let allRevenues := select 'Revenue';
        let allStores := select 'Stores';

        for revenue in allRevenues do
            let store := first((select allStores)[Name = revenue.'Store name']);
            revenue.store := store;
        end
    end

    • Wethrift
    • Nick_Drewe
    • 3 yrs ago
    • Reported - view

    On closer inspection, this isn't working, I was using slightly different names in my test code:

    let allPrograms := select 'All programs';
    let revenues := select Revenue;

    for revenue in revenues do
        let program := first((select allPrograms)[slug = revenue.slug]);
        revenue.'Assigned program' := program
    end

    Which is giving me the error: Table not found: allPrograms at line 5

     

    So back to my original question. Is there a way to make a sub-selection form a selection that is already assigned to a variable?

    • Wethrift
    • Nick_Drewe
    • 3 yrs ago
    • Reported - view

    I've eventually found a solution to do this using two nested loops. Going back to my original example, the first to loop through each revenue record, and the second to loop through each store record.

    Performance seems great.

    do as server

      let allRevenues := select 'Revenue';

      let allStores := select 'Stores';

     

      for revenue in allRevenues do

        for store in allStores do

    if store.Name = revenue.'Store name' then

            revenue.store = store;

          end

        end

      end

    end

    • Alain_Fontaine
    • 3 yrs ago
    • Reported - view

    I don't know if this would help you. Anyway: once you have put a set of records in an array, you can refine the content of the array with the regular restriction syntax. For example:

    let myArray := select ..... ;

    let myRestrictedArray := myArray[any valid condition]

    To say it otherwise, the trick is to omit the "select" keyword when restricting an existing record set.

Login to reply

Content aside

  • 1 Likes
  • 3 yrs agoLast active
  • 4Replies
  • 753Views