New post
Question
· Oct 3, 2017

Querying a list property with SQL

#Object Data Model #SQL #Caché

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
Vitaliy Serdtsev · Oct 4, 2017

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
2 0
Wolf Koelling · Oct 4, 2017

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))

2 0