What more can be done with lists in SQL (%DLIST, %INLIST, FOR SOME)

What I find really useful about IRIS when teaching my subject of Postrelational databases is the fact that it is a multi model database. Which means that I can actually go into architecture and structure and all that only once but then show the usage of different models (like object, document, hierarchy) using the same language and approach. And it is not a huge leap to go from an object oriented programming language (like C#, Java etc) to an object oriented database.

However, along with advantages (which are many) come some drawbacks when we switch from object oriented model to relational. When I say that you can get access to the same data using different models I need to also explain how it is possible to work with lists and arrays from object model in relational table. With arrays it is very simple - by default they are represented as separate tables and that's the end of it. With lists - it's harder because by default it's a string. But one still wants to do something about it without damaging the structure and making this list unreadable in the object model.

So in this article I will showcase a couple of predicates and a function that are useful when working with lists, and not just as fields.

Let's say that we have a class Restaurant.Dish which will have all our dishes:

Class Restaurant.Dish Extends %Persistent
{
Property Name As %String;
Property Description As %String(MAXLEN = 1000);
Property Category As %String;
Property Price As %Float;
Property Currency As %String;
Property Calories As %Integer;
}

Which we somehow populated with data:

And there is a class Restaurant.Account which contains the favourite dishes of the patrons:

Class Restaurant.Account Extends %Persistent
{
Property Name As %Name;
Property FavouriteFood As list Of %String;
}

And it also contains data:

First, let's look at the aggregate function %DLIST. It returns an ObjectScript %List structure containing the values in the specified column as list elements. In general, the syntax is as follows:

%DLIST([ALL | DISTINCT [BY(col-list)]] 
  string-expr 
  [%FOREACH(col-list)] [%AFTERHAVING])

Now, let's say we need to group all the dishes by categories and get a list of all dishes in those categories:

select Category,
       %DLIST(Name) AS AllDishes,
       %DLIST(Distinct (Name)) AS AllDistinctDishes
  from Restaurant.Dish
GROUP BY Category
ORDER BY Category

Another one is a predicate condition %INLIST. It matches a value to the elements in a %List structured list. It resembles the predicate IN but expects to get a $LB as an argument, instead of a values, separated by comas. Its syntax is as follows:

scalar-expression %INLIST list [SIZE ((nn))]

For example, now we want to see what dishes out of favourites of our patrons we have on a menu:

select Name, Description, Price
  from Restaurant.Dish
WHERE name %INLIST (select FavouriteFood 
                      from Restaurant.Account 
                     where ID = 1)  SIZE ((10))

And the last predicate condition I want to highlight here is FOR SOME %ELEMENT. It matches the list elements in field with the specified predicate. The SOME keyword specifies that at least one of the elements in the field must satisfy the specified predicate clause. The predicate clause must contain either the %VALUE or the %KEY keyword, followed by a predicate condition. These keywords are not case-sensitive. The syntax is as follows:

FOR SOME %ELEMENT(field) [[AS] e-alias] (predicate)

As for the last example, let's say we got a deal for Sprite and Diet Coke and we wish to see if it will have traction with our patrons. 

select *
  from Restaurant.Account 
 where FOR SOME %ELEMENT(FavouriteFood) f
     (f.%VALUE IN ('Sprite','Diet Coke') and %KEY IS NOT NULL)

So this is just to attract your attention to these 3 possibilities of working with lists in SQL. For further details, please visit the documentation pages I referenced in the article. And of course, you can always use built-in functions when working with $lb in SQL.