Introduction to Using Query Expressions

Session held at LDC Toronto May 2012.
Presented by Brad Lindsay, North Carolina Hospital Association

Query Expressions are one of Lasso 9's best new features. They allow for simple and complex interaction with and manipulation of various sequence types using a natural-language syntax. Understanding and effectively using query expressions is essential for every Lasso 9 developer.

Benefits & Drawbacks

There are several benefits of using query expressions:

1. With its natural-language syntax it produces more readable code.
2. It can simplify some common complex tasks.
3. In my testing of simple use cases, query expressions were 8.5 to 22 times faster than using an [iterate] block.

However, there are some things that may keep you from using them:

1. Can be confusing when you first see them.
2. You can't use [loop_abort] or [loop_continue] in them.
3. In simple use cases they are 4.5 times slower than a [loop] block and 10.5 times slower than a [while] block.
    

Query Expression Anatomy

Each query expression starts with a with clause that is similar to a combination of a SELECT and FROM clause in SQL or like an [iterate] block that creates its own local variable for each element it iterates over. Since it starts every query expression, this clause is required; it declares the name of a local variable that will store each element from the sequence and specifies the sequence being worked upon.

Following the with clause, the query expression can optionally have 1 or more operator clauses. These operations filter and adjust the data that the query expression will be working on.

The final required clause in a query expression is the action clause. In fact, all line breaks in a query expression are ignored until the action clause and the query expression is considered done when the action clause statement ends. The action clause specifies what work the query expression should do on the elements in the sequence.

With Clause

Again, the with clause starts every query expression and is in the form of "with local_var in #sequence". A sequence is anything that implements trait_queriable such as array and staticarray. This means that custom types can be used in query expressions too if they implement trait_queriable.

The scope of the local variable that gets created is limited to the query expression. This means that referencing this variable after the query expression will throw an error, and the value of a local variable of the same name created before the query expression will remain untouched.

Operations

The let operation creates another local variable that can be used inside the operation. It takes the form of "let varname = _value_" where "varname" is the name of the new local variable to create and "_value_" is the value to store in it. The scope is similar to that of the local variable created in the with clause — it only exists inside the query expression. In fact, it only exists for that single iteration of the expression and cannot reference the value it may have previously held in prior iterations.

    local(people) = array(
        map('first'='Moose', 'last'='Hair'),
        map('first'='Brad' , 'last'='Lindsay'),
        map('first'='Sean' , 'last'='Stephens')
    )
    with p in #people
    let  full_name = #p->find('first') + ' ' + 
                     #p->find('last')
    do { stdoutnl(#full_name) }
// Produces:
    Moose Hair
    Brad Lindsay
    Sean Stephens

The where operation is like an SQL WHERE clause, and it takes the form of "where _boolean expression_". It limits the elements in the sequence to those which match the logic in the _boolean expression_. Query expressions can have multiple where operations and each operation must be true for the element to be acted on. (In other words, it behaves as if they were ANDed together.)

    local(people) = array(
        map('first'='Moose', 'last'='Hair'),
        map('first'='Brad' , 'last'='Lindsay'),
        map('first'='Sean' , 'last'='Stephens')
    )
    with p in #people
    where #p->find('first')->size < 5
    where #p->find('first')->beginsWith('B') or
          #p->find('first') >> 'a'
    do { stdoutnl(#p->find('first')) }
// Produces
    Brad
    Sean

The skip operation takes the form of "skip _n_" and tells the query expression to ignore the next _n_ number of elements in the sequence. The reason I say "the next _n_ elements" is because it is possible that there are other operation clauses that have come before the skip clause that may have already caused some elements to be ignored.

    local(people) = array(
        map('first'='Moose', 'last'='Hair'),
        map('first'='Brad' , 'last'='Lindsay'),
        map('first'='Sean' , 'last'='Stephens')
    )
    with p in #people skip 1
    do { stdoutnl(#p->find('first')) }
// Produces
    Brad
    Sean

The take operation takes the form of "take _n_" and tells the query expression to limit itself to the next _n_ number of elements in the sequence. Again, just like with the skip operation, the set of elements that the take operation can draw on may have been limited by previous operations in the query expression.

    local(people) = array(
        map('first'='Moose', 'last'='Hair'),
        map('first'='Brad' , 'last'='Lindsay'),
        map('first'='Sean' , 'last'='Stephens')
    )
    with p in #people take 2
    do { stdoutnl(#p->find('first')) }
// Produces
    Moose
    Brad

The order by operation is similar to the SQL ORDER BY clause and takes the form of "order by _expression_ (ascending|descending)(, _expression_ (ascending|descending)…)". The "_expression_" is evaluated and the sequence is then sorted by that value. Optionally the key words ascending and descending can be used to specify the direction of the sorting with the default being ascending. The sequence can be sorted further by using multiple _expression_’s separated by a comma.

    local(people) = array(
        map('first'='Moose', 'last'='Hair'),
        map('first'='Brad' , 'last'='Lindsay'),
        map('first'='Sean' , 'last'='Stephens')
    )
    with p in #people
    order by #p->find('first')
    do { stdoutnl(#p->find('first')) }
//Produces
    Brad
    Moose
    Sean

There is also a group by operation, but its use is complex and therefore beyond the scope of this introduction.

Actions

The min action takes the form of "min _expression_" and returns the minimum value from the sequence for the evaluated _expression_’s.

    local(people) = array(
        map('first'='Moose', 'last'='Hair'),
        map('first'='Brad' , 'last'='Lindsay'),
        map('first'='Sean' , 'last'='Stephens')
    )
    with p in #people
    min #p->find('last')->size
// Produces
    4

The max action takes the form of "max _expression_" and returns the maximum value from the sequence for the evaluated _expression_’s.

    local(people) = array(
        map('first'='Moose', 'last'='Hair'),
        map('first'='Brad' , 'last'='Lindsay'),
        map('first'='Sean' , 'last'='Stephens')
    )
    with p in #people max #p->find('last')->size
//Produces
    8

The sum action takes the form of "sum _expression_" and returns the value of adding up all the evaluated _expression_’s.

    local(people) = array(
        map('first'='Moose', 'last'='Hair'),
        map('first'='Brad' , 'last'='Lindsay'),
        map('first'='Sean' , 'last'='Stephens')
    )
    with p in #people 
    sum #p->find('last')->size
// Produces
    19

The average action takes the form of "average _expression_" and returns the value of adding up all the evaluated _expression_’s and then dividing them by the number of elements in the addition.

    local(people) = array(
        map('first'='Moose', 'last'='Hair'),
        map('first'='Brad' , 'last'='Lindsay'),
        map('first'='Sean' , 'last'='Stephens')
    )
    with p in #people 
    average #p->find('last')->size
// Produces
    6

The select action takes the form of "select _expression_" and returns a new sequence based on evaluating the "_expression_" for each element. The type of this new object is a queriable_select.

    local(people) = array(
        map('first'='Moose', 'last'='Hair'),
        map('first'='Brad' , 'last'='Lindsay'),
        map('first'='Sean' , 'last'='Stephens')
    )
    with p in #people 
    select #p->find('last')
// Produces
    Hair, Lindsay, Stephens

The do action takes the form of "do _expression_" where the "_expression_" is either a single expression or a code block.

    local(people) = array(
        map('first'='Moose', 'last'='Hair'),
        map('first'='Brad' , 'last'='Lindsay'),
        map('first'='Sean' , 'last'='Stephens')
    )
    with p in #people 
    do { stdoutnl(#p->find('last')) }
// Produces
    Hair
    Lindsay
    Stephens

Useful Real-World Examples

The code below iterates over the elements in a map to create an UPDATE statement to be run on a database. Keep in mind that the order of elements in the map is unspecified. Of course an order can be specified with an order by operation, but there is no way to specify "the order in which I put the elements in the map".

local(person) = map(
    'id'   = 12345,
    'first'='Moose', 
    'last' ='Hair',
    'email'='mhair@rhino.org',
    'phone'='555-555-5555'
)
local(update_SQL) = "UPDATE people SET " + 
    with elm in #person->eachPair
    where #elm->first != 'id'
    sum '`' + #elm->first + "`='" + string(#elm->second)->encodesql + "',"
#update_SQL->removeTrailing(',')
#update_SQL += " WHERE id=" + integer(#person->find('id'))

The example below is taken from code that expects an action_param named “exclude” to contain a comma-separated list of integers representing ID values. Since we are security conscious, we need to prevent SQL injection attacks, and the code below should help us do just that. The code creates a SELECT statement finding all rows whose ID is not contained in the list passed via the "exclude" action parameter.

local(find_SQL) = "SELECT * FROM people WHERE id NOT IN(" + 
    (with id in action_param('exclude')->split(',') 
     select integer(#id)
    )->join(',') + 
")"

The example below shows how to use query expression for a simple loop. While it may turn out that a [loop] block is faster in these simple instances, it may be slower for more complex code. Also, for small datasets, the speed difference may not matter.

local(people) = array(
    map('first'='Moose', 'last'='Hair'),
    map('first'='Brad' , 'last'='Lindsay'),
    map('first'='Sean' , 'last'='Stephens')
)
with i in 1 to #people->size
select #people->get(#i)->find('last')

The last example shows how to use the scope rules mentioned above to your advantage and create a replacement for [loop_count] — in this case the local variable #i. The code itself is taking in a URL in the form of "/resource/id/subresource/id" and returning the value "resource/subresource/".

local(url_path) = "/people/83/address/2"
local(i) = 1
with item in #url_path->split('/')
let i = #i++
where (#i % 2) == 0
sum #item + '/'

Conclusion

Query expressions are a great new addtion to the Lasso language which every developer should be familiar with and have in their toolbox.

Author: Brad Lindsay
Created: 25 May 2012
Last Modified: 4 Jun 2012