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:
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
but the SQL-projection is array-like
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:
a4) Compile the class
a5) Change the property definotion to list:
a6) Compile the class
Voila, you got a list-table property:
b) 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:
change the property name in the storage definition too
then add the new property as array
b2) Compile the class
b3) Change the property's collection from array to list
b4) Compile the class
b5) Transfer the list data from old storage to the new and potentially delete the old list data
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
c2) Compile the class
c3) Change the new property collection from array to list
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.
Some examples after do ##class(..).Poulate(1E6)
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?
Index numDossiersMERIdx On numDossiersMER(ELEMENTS);
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:
(Please excuse the formatting)
Hi Lucas,
A simple solution to you question can be this :
With those parameters you can achieve :
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