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.
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.
Thanks, this is ultimately what I ended up doing and it works well enough for my purposes.
Doesn't it work only for datatype properties though?
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.
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).
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:
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))
Social networks
InterSystems resources
Log in or sign up
Log in or create a new account to continue
Log in or sign up
Log in or create a new account to continue
Log in or sign up
Log in or create a new account to continue