Post-Processing Database Results Using Arrays and Matchers

This article discusses how Lasso can be used to post-process database results using array tools. The tip shows how a custom matcher can be used to filter elements from one array into a new array.

Introduction

The [Records_Array] tag provides an array of database results from any Lasso inline. This data structure can be used to quickly output the results of a database query or as the input for algorithmic processing of the database results. (The [Records] ... [/Records] and [Field] tags are preferred for the display of database results.)

However, Lasso does not provide a direct way to perform a sub-query to find only select rows within a larger result set generated by a database query. This tip includes some code which makes it easy to filter a [Records_Array] to select only those records that meet additional criteria.

Records Array

The [Records_Array] tag returns an array of arrays which represent all the records that are returned from a database query. For example, we might have a database query that returns the following four records:

[Inline: -Database='Contacts', -Table='People', -FindAll]
  [Records]
    [Field: 'First'] [Field: 'Last'] [Field: 'Age']
  [/Records]
[/Inline]

 

  John Doe 21
  Jane Doe 22
  Bob Surname 45
  Tia Lastname 35

 

Instead of printing out the results we can capture them into a variable $myRecordsArray using the following code. We also capture the field names into $myFieldNames.

[Inline: -Database='Contacts', -Table='People', -FindAll]
  [Var: 'myRecordsArray' = (Records_Array)]
  [Var: 'myFieldNames' = (Field_Names)]
[/Inline]

 

The structure of $myRecordsArray and $myFieldNames is essentially as follows.

[Var: 'myRecordsArray' = (Array:
	(Array: 'John', 'Doe', '21'),
	(Array: 'Jane', 'Doe', '22'),
	(Array: 'Bob', 'Surname', '45'),
	(Array: 'Tia', 'Lastname', '35'),
)]
[Var: 'myFieldNames' = (Array: 'First', 'Last', 'Age')]

 

Sub-Queries

We can perform a sub-query by finding select records from our records array and storing those into a new array. This could be done using [Iterate] ... [/Iterate] to select elements from the array, but for this tip we will instead construct a matcher which allows us to filter the array into a new array in one step.

For example, we would like to extract all of the people with a last name of "Doe" from the array. We can do this by returning any elements from the array whose second element is "Doe". We use a custom matcher [Ex_Match_Element] (defined below) which takes two parameters: a position and a value. The position is 2 to select the second element from the sub-arrays and the value is "Doe"

  [Var: 'mySubQuery' = $myRecordsArray->(Find:
    (Ex_Match_Element: 2, 'Doe'))]

The result of running this code is an array of two elements, one for John Doe and one for Jane Doe.

   (Array: (Array: 'John', 'Doe', '21'), (Array: 'Jane', 'Doe', '22'))

One difficulty with this code is that we have hard-coded the field position 2. It would be preferable to use the field name "Last" to select what record we wanted to compare. The following code looks up the field name last and returns its position within the [Field_Names] array. Then we use that value $LastField in our sub-query code and get the same results as above.

  [Var: 'LastField' = $myFieldNames->(FindPosition: 'Last')->First]
  [Var: 'mySubQuery' = $myRecordsArray->(Find:
    (Ex_Match_Element: $LastField, 'Doe'))]

Numeric Comparison

We can use a built-in matcher to provide a more complex sub-query. Say we want to find all people from our query who are between the ages of 30 and 50. The [Match_Range] matcher will find numeric values that are within a given range. [Match_Range: 30, 50] will find numeric values greater than or equal to 30 and less than or equal to 50.

The following code finds the position of the "Age" field within the field names then uses a combination of [Ex_Match_Element] and the built-in [Match_Range] to find the records which have an age in our desired range.

  [Var: 'AgeField' = $myFieldNames->(FindPosition: 'Age')->First]
  [Var: 'mySubQuery' = $myRecordsArray->(Find:
    (Ex_Match_Element: $AgeField, (Match_Range: 30,50)))]

   (Array: (Array: 'Bob', 'Surname', '45'), (Array: 'Tia', 'Lastname', '35'))

Regular Expressions

We can also use the built-in [Match_RegExp] matcher to perform regular expression searches for our sub-query. This allows us to do begins with, contains, ends with, searches, etc. For example, we can find all records whose first name begins with A through M. The regular expression looks like "[A-M].*", that is any letter A through M followed by any other string. The result is three records.

  [Var: 'FirstField' = $myFieldNames->(FindPosition: 'First')->First]
  [Var: 'mySubQuery' = $myRecordsArray->(Find:
    (Ex_Match_Element: $FirstField, (Match_RegExp: '[A-M].*')))]

   (Array: (Array: (Array: 'John', 'Doe', '21'), (Array: 'Jane', 'Doe', '22'), (Array: 'Bob', 'Surname', '45'))

Implementation

The implementation of the above examples requires the custom matcher [Ex_Match_Element]. A matcher is a custom data type which has one purpose: when it is compared to a value it returns whether it matches the value or not. The matcher actually returns 0 if it matches the value, 1 if it is greater than the value, and -1 if it is less than the value. This code defines the matcher. The onCreate tag stores the parameters for the type. The onCompare tag is called when the matcher is used in [Array->Find] and returns 0 if a match is made or -1 otherwise.

--! Lassoscript goes here --!
  define_type: 'Ex_Match_Element';
    local: 'position';
    local: 'value';
    define_tag: 'oncreate', -required='position', -required='value';
      self->'position' = #position;
      self->'value' = #value;
    /define_tag;
    define_tag: 'oncompare', -required='value';
      return: self->'value' == #value->(get: self->'position') ? 0 | -1;
    /define_tag;
  /define_type;
?>

The following code runs each of the examples from this tip. This code and the [Ex_Match_Element] code should be pasted in the same file to see how all of the examples work.

--! Lassoscript goes here --!
  // Note - The records array is generated synthetically for this example.

  Var: 'myRecordsArray' = (Array:
    (Array: 'John', 'Doe', '21'),
    (Array: 'Jane', 'Doe', '22'),
    (Array: 'Bob', 'Surname', '45'),
    (Array: 'Tia', 'Lastname', '35'),
  );
  Var: 'myFieldNames' = (Array: 'First', 'Last', 'Age');
  
  '<hr />Sub-Queries<br />';
  Var: 'LastField' = $myFieldNames->(FindPosition: 'Last')->First;
  Var: 'mySubQuery' = $myRecordsArray->(Find:
    (Ex_Match_Element: $LastField, 'Doe'));
  Var: 'mySubQuery';
  
  '<hr />Numeric Comparison<br />';
  Var: 'AgeField' = $myFieldNames->(FindPosition: 'Age')->First;
  Var: 'mySubQuery' = $myRecordsArray->(Find:
    (Ex_Match_Element: $AgeField, (Match_Range: 30,50)));
  Var: 'mySubQuery';

  '<hr />Regular Expressions<br />';
  Var: 'FirstField' = $myFieldNames->(FindPosition: 'First')->First;
  Var: 'mySubQuery' = $myRecordsArray->(Find:
    (Ex_Match_Element: $FirstField, (Match_RegExp: '[A-M].*')));
  Var: 'mySubQuery';
?>

Author: Fletcher Sandbeck
Created: 8 Aug 2008
Last Modified: 16 Mar 2011