Question
· Oct 3, 2017

Querying a list property with SQL

I have a class that has a list property, which contains a list of other objects, and I want to join against it in SQL.

Class Foo Extends %Persistent
{
Property MyBars As list Of Bar;
}

Class Bar Extends %Persistent { Property Name As %String; }

Simply querying the Foo table, I see that MyBars looks like a $LIST, so I tried using a query with the %inlist operator but that didn't seem to work as expected. The following query produces zero results:

select bar.name from foo join bar on bar.id %INLIST MyBars

Is there some convenient way to do this sort of join? I'm looking for a result that has a row for each element in each of the MyBars lists in all my Foos.

Discussion (7)0
Log in or sign up to continue

You have to go the other way:

SELECT * FROM Foo JOIN Bar on Foo.MyBars [ Bar.ID

Note [ is the ObjectScript "Contains" operator.


The typical recommendation is that if you care at all about the relational structures of your tables, you will NOT use lists.  You are far better off using Arrays, which project as child tables, or one-to-many relationships, if applicable.

The following query produces zero results:

select bar.name from foo join bar on bar.id %INLIST MyBars

This is because MyBars is stored as a $lb($lb(id1),$lb(id2),...,$lb(idN)), and you try to query the data stored in the form $lb(id1,id2,...,idN).

Your query need some change to make it work, namely:

select name from foo join bar b on $listbuild(b.%ID) %INLIST MyBars

Yes, you can do what you want with a list property, you don't need an array property for it, even though that would make your life easier.

First of all, for optimisation purposes, add an index on your list items:

Index Bar On MyBars(ELEMENTS);
 

Then use this correlated subquery:

SELECT Name
FROM Bar
WHERE EXISTS
(SELECT ID FROM Foo WHERE FOR SOME %ELEMENT(Foo.MyBars) (%VALUE = Bar.ID))