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 one Property numDossierMER As %Integer; /// New field which is a list Property numDossiersMER As list Of %Integer; Index titreIdx On (numTitre, millesime, codeProduit) [ PrimaryKey ]; /// Old index Index numDossierMERIdx On numDossierMER; /// New index Index 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
Product version: Ensemble 2017.1
$ZV: Cache for Windows (x86-64) 2017.2.1 (Build 801U) Wed Dec 6 2017 09:07:51 EST
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_d...
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_MyPropb) You want to keep your data and you want to retain the property name numDossiersMER (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: .894Alright,
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
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.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 :
select numDossierMER, numDossiersMER from User_TestList_Data.Titre where for some %element(numDossiersMER) (%Value in (345))