Back
  1. Home
  2. Querying a list property with SQL

Querying a list property with SQL

Primary tabs

Arcady Goldmints-Orlov's picture
Arcady Goldmint... 3 October 2017
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.

 

Answers

Vitaliy Serdtsev's picture
Answer by Vitaliy Serdtsev 4 October 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
Accepted answer
Arcady Goldmints-Orlov's picture
Comment by Arcady Goldmint... 4 October 2017

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

0 0
Warlin Garcia's picture
Answer by Warlin Garcia 3 October 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
Kyle Baxter's picture
Answer by Kyle Baxter 3 October 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's picture
Answer by Wolf Koelling 4 October 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
Wolf Koelling's picture
Comment by Wolf Koelling 4 October 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