Separate list results of persistent classes from SQL
Hello everyone,
We are trying out lists inside persistent classes. Because we may need to use them in a very close future.
So far, I managed to create a class that has a list property which has an indice.
Here is the class in question:
Class User.TestList.Data.Titre Extends (%Persistent, %Populate){Property numTitre As %Integer;
Property millesime As %Integer;
Property codeProduit As %String;
/// Old field which will be replaced by the next oneProperty numDossierMER As %Integer;/// New field which is a listProperty numDossiersMER As list Of %Integer;Index titreIdx On (numTitre, millesime, codeProduit) [ PrimaryKey ];/// Old indexIndex numDossierMERIdx On numDossierMER;/// New indexIndex numDossiersMERIdx On numDossiersMER(ELEMENTS);
}
I also managed to query the table depending on values of the list with the following query structure:
SELECT ID, codeProduit, millesime, numDossierMER, numDossiersMER, numTitre FROM User_TestList_Data.Titre WHERE FOR SOME %ELEMENT(numDossiersMER) (%VALUE IN (24, 662703520))
Altough, one question remained unanswered so far:
How can I fetch results from the table from SQL and split all elements of the list into separate lines ?
For example, let's say I only have two lines in my table.
If I query the table with a simple SELECT * FROM User.TestList.Data.Titre. The results will be as follows:
| numTitre | millesime | codeProduit | numDossierMER | numDossiersMER (list) |
|---|---|---|---|---|
| 1 | 2021 | X | 1 | 1 2 3 |
| 2 | 2021 | X | 4 | 4 5 6 |
How can I get the results to be formatted like this instead ?
| numTitre | millesime | codeProduit | numDossierMER | numDossiersMER (list) |
| 1 | 2021 | X | 1 | 1 |
| 1 | 2021 | X | 1 | 2 |
| 1 | 2021 | X | 1 | 3 |
| 2 | 2021 | X | 4 | 4 |
| 2 | 2021 | X | 4 | 5 |
| 2 | 2021 | X | 4 | 6 |
Thank you
Lucas
Comments
If that's what you want to do, you may want to consider using an array, not a list. By default, arrays are projected as a child table for SQL. You can find more details on the differences in the Working with Collections article, and the part I'm referring to specifically is the Default Projection of Array Properties section.
You could also change the storage default parameter of your list to "array", which is also described in the above article.
There are two solutions, either you use the property numDossiersMER as array instead of list, as suggested by David Hockenbroch, or in case when existing application use list methods like insert and FOR-loops to acces list elements, then you can change this property to a kind of list-table property (see below).
Either of the above gives you the possibility to use queries like:
select Titre->ID, Titre->numTitre, Titre->millesime, Titre->codeProduit, Titre->numDossierMer, numDossiersMER
from User_TestList_Data.Titre_numDossiersMER
where numDossiersMER in (123, 234, 345)The following guidance is based on the fact that Cache/IRIS uses the so called "schema evolution" in class storage, see also: https://docs.intersystems.com/latest/csp/docbook/Doc.View.cls?KEY=GOBJ_…
I use to say list-table property if in a class definition a property shows up as
Property PropName As list of WhateverDataType;but the SQL-projection is array-like
Property PropName As array Of WhateverDataType;The steps to create a list-table property depends on the state of your project:
a) You not yet have any data (or the data you have can be deleted):
a1) Delete the possibly existing data
a2) Delete the storage definition (Studio-->Inspector-->Storage-->RightClick-->Delete)
a3) Change the property definition to array:
Property numDossiersMER As array of %Integer;a4) Compile the class
a5) Change the property definotion to list:
Property numDossiersMER As list Of %Integer;a6) Compile the class
Voila, you got a list-table property:
do obj.MyProp.Insert(data) to add data items
and query property data as it would be a table: select * from class.name_MyProp b) You want to keep your data and you want to retain the property namenumDossiersMER (because you don't want to change existing applications). Before proceeding, make a backup of your class globals, then:
b1) Rename the existing property and then add it again as a new array property:
from: Property numDossiersMER as list of %Integer
to : Property OLDnumDossiersMER as list of %Integerchange the property name in the storage definition too
from: <Value>numDossiersMER</Value>
to : <Value>OLDnumDossiersMEROLD</Value>then add the new property as array
Property numDossiersMER as array of %Integer;b2) Compile the class
b3) Change the property's collection from array to list
Property numDossiersMER as list of %Integer;b4) Compile the class
b5) Transfer the list data from old storage to the new and potentially delete the old list data
set id=0
for {set id=$order(^User.TestList.Data.TitreD(id)) quit:'id
set obj=##class(User.TestList.Data.Titre).%OpenId(id)
if 'obj write id," ??",! continue
for i=1:1:obj.OLDnumDossiersMER.Count() do obj.numDossiersMER.Insert(obj.OLDnumDossiersMER.GetAt(i)
// obj.OLDnumDossiersMER.Clear()
do obj.%Save()
}or you use an SQL statement instead of $order(...)
b6) Rebuild the indexes.
c) You want to keep your data and you want to have a new property name too. Again, before proceeding, make a backup of your class globals, then:
c1) Add the new property as an array
Property numNewDossiersMER As array Of %Integer;c2) Compile the class
c3) Change the new property collection from array to list
Property numNewDossiersMER As list Of %Integer;c4) Compile the class
c5) Transfer the list data from numDossiersMER to numNewDossiersMER according to b5)
It's IMPORTANT to follow the above steps in the given sequence!
Just to keep things complete, the other way around (array items stored as list items) is also possible. You have just to swap the definition sequence: define as list, compile, redefine as array, compile.
Thank you for your answer Julius Kavay.
I'm using your solution.
But unfortunately, I'm facing another problem. The indexes are not working.
Index numDossiersMERIdx On numDossiersMER(ELEMENTS);
Index numDossiersMERKeyOn numDossiersMER(KEYS);
I added both lines to my class, but it's not used by IS.
How can I put an index on numDossiersMER elements and use it ?
Thank you
There is a keyword %NOINDEX indexname1, indexname2, ... to prevent the SQL-Enginne to use specific indices but there is no keyword for the opposite, something like %USEINDEX indexname, sadly.
Maybe someone with more SQL experience knows what is preventing the SQL engine to use the existing index over the numDossiersMER property...
But, and this is the great thing with IRIS and Cache, if everything else fails, you can always create your custom query.
Class User.TestList.Data.Titre Extends (%Persistent, %Populate)
{
Property numTitre As %Integer;
Property millesime As %Integer;
Property codeProduit As %String;
/// Old field which will be replaced by the next one
Property numDossierMER As %Integer;
Property numDossiersMER As list Of %Integer;
Index titreIdx On (numTitre, millesime, codeProduit) [ PrimaryKey ];
/// Old index
Index numDossierMERIdx On numDossierMER;
Index numDossiersMERIdx On numDossiersMER(ELEMENTS);
Query Select(num...) As %Query(CONTAINID = 1, ROWSPEC = "ID:%Integer,Dossier:%Integer,codeProd:%String") [ SqlProc ]
{
}
ClassMethod SelectExecute(par As %Binary, num...) As %Status
{
kill par, ^||tmpSelectQry
for i=1:1:$g(num) set nr=$g(num(i)) merge:nr]"" ^||tmpSelectQry(nr)=^User.TestList.Data.TitreI("numDossiersMERIdx",nr)
set par=$na(^||tmpSelectQry)
quit $$$OK
}
ClassMethod SelectFetch(par As %Binary, row As %List, end As %Integer) As %Status
{
set par=$query(@par)
if par="" { set end=1, row="" }
else {
set end=0, id=$qs(par,2)
set row=$lb(id, $qs(par,1), ..codeProduitGetStored(id)) // and other fields...
}
quit $$$OK
}
ClassMethod SelectClose(par As %Binary) As %Status
{
kill par, ^||tmpSelectQry
quit $$$OK
}
ClassMethod Test()
{
write "Using a ResultSet...",!
set rs=##class(%ResultSet).%New("User.TestList.Data.Titre:Select")
if rs.Execute(230,3590,40110,507550,6094,70071,820096,9380148,8,592) {
set t=$zh
while rs.Next() { write rs.Data("ID"),?10,rs.Data("Dossier"),?30,rs.Data("codeProd"),! }
}
write "Time: ",$zh-t*1E3,!!
write "Direct usage of the query methods...",!
do ..SelectExecute(.par,230,3590,40110,507550,6094,70071,820096,9380148,8,592)
set t=$zh
for do ..SelectFetch(.par,.row,.end) quit:end zwrite row
write "Time: ",$zh-t*1E3,!
}
Storage Default
{
<Data name="TitreDefaultData">
<Value name="1">
<Value>%%CLASSNAME</Value>
</Value>
<Value name="2">
<Value>numTitre</Value>
</Value>
<Value name="3">
<Value>millesime</Value>
</Value>
<Value name="4">
<Value>codeProduit</Value>
</Value>
<Value name="5">
<Value>numDossierMER</Value>
</Value>
</Data>
<Data name="numDossiersMER">
<Attribute>numDossiersMER</Attribute>
<Structure>subnode</Structure>
<Subscript>"numDossiersMER"</Subscript>
</Data>
<DataLocation>^User.TestList.Data.TitreD</DataLocation>
<DefaultData>TitreDefaultData</DefaultData>
<ExtentSize>1000000</ExtentSize>
<IdLocation>^User.TestList.Data.TitreD</IdLocation>
<IndexLocation>^User.TestList.Data.TitreI</IndexLocation>
<Property name="%%CLASSNAME">
<AverageFieldSize>1</AverageFieldSize>
<Selectivity>100.0000%</Selectivity>
</Property>
<Property name="%%ID">
<AverageFieldSize>5.88</AverageFieldSize>
<Selectivity>1</Selectivity>
</Property>
<Property name="codeProduit">
<AverageFieldSize>4.89</AverageFieldSize>
<Selectivity>0.0004%</Selectivity>
</Property>
<Property name="millesime">
<AverageFieldSize>8.89</AverageFieldSize>
<Selectivity>0.0001%</Selectivity>
</Property>
<Property name="numDossierMER">
<AverageFieldSize>8.89</AverageFieldSize>
<Selectivity>0.0001%</Selectivity>
</Property>
<Property name="numTitre">
<AverageFieldSize>8.89</AverageFieldSize>
<Selectivity>0.0001%</Selectivity>
</Property>
<SQLMap name="IDKEY">
<BlockCount>-63088</BlockCount>
</SQLMap>
<SQLMap name="numDossierMERIdx">
<BlockCount>-7912</BlockCount>
</SQLMap>
<SQLMap name="titreIdx">
<BlockCount>-19940</BlockCount>
</SQLMap>
<StreamLocation>^User.TestList.Data.TitreS</StreamLocation>
<Type>%Storage.Persistent</Type>
} }
Some examples after do ##class(..).Poulate(1E6)
USER>d ##class(User.TestList.Data.Titre).Test()
Using a ResultSet...
700556 8 R7369
696384 230 R6776
952257 592 E8624
209184 3590 Q7863
239874 6094 N7969
497500 40110 W6490
188796 70071 O9708
145090 507550 S3705
803994 820096 S20
97986 9380148 W6598
Time: .787
Direct usage of the query methods...
row=$lb("700556","8","R7369")
row=$lb("696384","230","R6776")
row=$lb("952257","592","E8624")
row=$lb("209184","3590","Q7863")
row=$lb("239874","6094","N7969")
row=$lb("497500","40110","W6490")
row=$lb("188796","70071","O9708")
row=$lb("145090","507550","S3705")
row=$lb("803994","820096","S20")
row=$lb("97986","9380148","W6598")
Time: .894
Alright,
I think the "FOR SOME %ELEMENT" will do just fine when querying the table based on elements of the list (and therefore using the indices).
The fact that we can display the values in distinct lines is already great.
Thank you for help
Did you perform "Purge cached queries" before your test?
I tried it with some test data and could see the expected query plan using
Index numDossiersMERIdx On numDossiersMER(ELEMENTS);
.png)
The above solution is great if you’d like to keep the list in each record of one table. Depending on the nature of the data in your application, another option is to “normalize” the data a bit and create a separate table for the “numDossiersMER” values and link them back to the original “Titre” table as follows.
Convert the planned updated table from this:
User.TestList.Data.Titre
|
numTitre |
millesime |
codeProduit |
numDossiersMER (list) |
|
1 |
2021 |
X |
1 2 3 |
|
2 |
2021 |
X |
4 5 6 |
|
3 |
2021 |
X |
4 2 3 |
|
4 |
2022 |
X |
2 5 7 8 |
To the following 2 normalized tables
User.TestList.Data.TitreNew
|
Id (IRIS) |
numTitre |
millesime |
codeProduit |
|
1 |
1 |
2021 |
X |
|
2 |
2 |
2021 |
X |
|
3 |
3 |
2021 |
X |
|
4 |
4 |
2022 |
X |
User.TestList.Data.DossierMER
|
Id (IRIS) |
titreID |
numDossierMER |
|
1 |
1 |
1 |
|
2 |
1 |
2 |
|
3 |
1 |
3 |
|
4 |
2 |
4 |
|
5 |
2 |
5 |
|
6 |
2 |
6 |
|
7 |
3 |
4 |
|
8 |
3 |
2 |
|
9 |
3 |
3 |
|
10 |
4 |
2 |
|
11 |
4 |
5 |
|
12 |
4 |
7 |
|
13 |
4 |
8 |
The “id (IRIS)” in each table is the “ROWID” assigned by IRIS as each entry is created in the table.
Using these two tables, the following “JOIN” query will get the results to be formatted as you like:
select numTitre, millesime, codeProduit, numDossierMER from User_TestList_Data.TitreNew t JOIN User_TestList_Data.DossierMER d on d.TitreId = t.id
Please note that this “normalized” solution is great if the “numDossierMER” values can be shared among various “Titre” records as shown in my made-up example above.
Sample code here:
Class User.TitreNew Extends (%Persistent, %Populate){Property numTitre As %Integer;Property millesime As %Integer;Property codeProduit As %String;Index titreIdx On (numTitre, millesime, codeProduit) [ PrimaryKey ];
}Class User.DossierMER Extends (%Persistent, %Populate){Property titreID As %Integer;Property numDossierMER As %Integer;
}(Please excuse the formatting)
Hi Lucas,
A simple solution to you question can be this :
Property numDossiersMER As list Of %Integer(SQLPROJECTION = "table/column", STORAGEDEFAULT = "array");
Index numDossiersMERIdx On numDossiersMER(ELEMENTS);
With those parameters you can achieve :
- 'As list Of %Integer' allows you to use the Insert() in ObjectScript and 'for some %element' in SQL command
- 'SQLPROJECTION = "table/column"' allows you to display the table as a column (note, the column does not appear in a select * it must be specified : select numDossierMER, numDossiersMER from User_TestList_Data.Titre )
- 'STORAGEDEFAULT = "array"' allows a separate table for the normalized representation
- 'Index numDossiersMERIdx On numDossiersMER(ELEMENTS);' bring the ability to use index on values with this SQL query :
select numDossierMER, numDossiersMER from User_TestList_Data.Titre
where
for some %element(numDossiersMER) (%Value in (345))