Question
Arcady Goldmint... · 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.

 
0
0 1,983
Discussion (7)1
Log in or sign up to continue
Warlin Garcia · Oct 3, 2017

The easiest way would be to change the definition from list to array. But if you can't do that the next easier thing is to change the STORAGEDEFAULT parameter to 'array'.  Both options will project the collection as a separate SQL table that you can join with.

0 0
Arcady Goldmint... · Oct 4, 2017

Thanks, this is ultimately what I ended up doing and it works well enough for my purposes.

0 0
Eduard Lebedyuk · Oct 4, 2017

Doesn't it work only for datatype properties though?

0 0
Kyle Baxter · Oct 3, 2017

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.

0 0
Wolf Koelling · Oct 4, 2017

No, this will work perfectly fine.

When creating object instances of Foo and Bar we'll insert Bar orefs into the MyBar object list collection of Foo - but via SQL / the relational projection id keys are being used instead (and, indeed, that's what get's stored in the globals anyway).

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

1 0