scripts to obtain the result set of ID for child table

Hi,

I've tryed to modify one of your scripts to obtain the result set of ID, for not main table, but for child table. unfortunately I can't get it, help me please:

import com.im.ijc.core.api.lists.TemporaryListsAndQueries



//++++++++++++++++++++++++++++++++++++++  main table

def parent = dataTree.rootVertex.entity

def edp = parent.schema.dataProvider.getEntityDataProvider(parent)

def rows = edp.getRowCount(env)

println "root has $rows rows"



def rs = parent.schema.dataProvider.getDefaultResultSet(dataTree, false, DFEnvironmentRO.DEV_NULL)

def parentVS = rs.getVertexState(dataTree.rootVertex)

def ids = parentVS.ids

//list of root id's

println " $ids"



//++++++++++++++++++++++++++++++++++++++  child table

def parentL = dataTree.rootVertex.edges.find {it.destination.entity.name == 'ChildTable'}

def protoEntity = parentL.destination.entity

def edpL = protoEntity.schema.dataProvider.getEntityDataProvider(protoEntity)

def rowsl = edpL.getRowCount(env)

println "child has $rowsl rows"



def rsl =  protoEntity.schema.dataProvider.getDefaultResultSet(dataTree, false, DFEnvironmentRO.DEV_NULL)

def parentVSl = rsl.getVertexState(dataTree.rootVertex)

def idsl = parentVSl.ids

println " $idsl"



//#######################################################################################################################





//change the number of records per list

lstidcnt = 50;



def i = 1, x = 0, bad = 0, y = 0;



List  listid = [];



 idsl.each { id ->

 println " $id"}



/*try {

    idsl.each { id ->



            // stop if the script is terminated

        if (env.getFeedback().isCancelled()) 

        {

            def msg = "Exporting data to file interupted in the record $good!"

            println msg

            throw new InterruptedException(msg)

        }



        try {

              if (i % lstidcnt != 0) 

                 {

                    listid << id; 

                    //println "$listid"

                 }

                 

              else {

                     listid << id;                     

                     TemporaryListsAndQueries.getListsFor(protoEntity).addList(listid, "list-" + x )

                     x++;

                     listid.clear();

                     //println "$listid"

                   }     

              i++;

            } 

        catch (Exception exc) {println "EROROR Failed to load ID $id ${exc.toString()}"}

        y++;

    }

    //если массив содержит меньше строк чем длина листа то он все равно запишется

    if (listid.size() >0){  TemporaryListsAndQueries.getListsFor(protoEntity).addList(listid, "list-" + x )} 

} finally { }



println "bad: $bad, good $y"*/

Script returns next:

Executing Groovy script 'ListPart'



root has 805 rows

 [179559, 179561, 179562, 179563]

child has 1397 rows

 [179559, 179561, 179562, 179563]

 179559

 179561

 179562

 179563



Script execution took 51 ms

The one of task for this script - it should from current result set of IDs (in child table and in main table) divide it to lists with limited  number of ID per list, but now I have "commented" the main code for child table list generation, cos, I cant obtain ID list of child table. (for example, if we got result of 15 ID for child tbl , and limit is 7 per list - we should get 3 lists with: 7, 7 and 1 ID and for main tbl: if we got result of 4 ID , and limit is 4 per list - we should get 1 list  with: 4 ID). 

and 2nd: is simple way, to add not temporary list, but permanent?

thanks!

Igor

Hi Igor,

In the part of code below, you have parentVSl defined to rootVertex, but you want to get child VertexState.

def rsl =  protoEntity.schema.dataProvider.getDefaultResultSet(dataTree, false, DFEnvironmentRO.DEV_NULL)

def parentVSl = rsl.getVertexState(dataTree.rootVertex)

def idsl = parentVSl.ids

println " $idsl"

To correct it, change it as follows. You will get destination property on parentL Edge. From that you obtain ResultSet.VertexState on the child table connected by that Edge. 

def parentVSl = rsl.getVertexState(parentL.destination)

A permanent list can be created by the following code snippet. It works standalone, if you copy it to new data tree script:

def ety = dataTree.rootVertex.entity



def firstValues = [1500]



ety.schema.userLockable.withLock('updating list') { envRW ->



    def nt = ety.getLists().getNewTypes().get(0);

    def options = nt.getOptions();

    options.setField(ety.getIdField());

    options.setValues(firstValues);

    options.setNewDFItemNameSafe("listovi2");

    //it is enough to call just nt.create(envRW) here, the rest is for obtaining reference to created list

    createdList = nt.create(envRW).iterator().next()

}

Hope it helps!

Regards,
Filip 

Hi, Filip!

Thanks for the ansver!

I've changed the script, but it also returns empty array:

import com.im.ijc.core.api.lists.TemporaryListsAndQueries



//++++++++++++++++++++++++++++++++++++++  main table

def parent = dataTree.rootVertex.entity

def edp = parent.schema.dataProvider.getEntityDataProvider(parent)

def rows = edp.getRowCount(env)

println "root has $rows rows"



def rs = parent.schema.dataProvider.getDefaultResultSet(dataTree, false, DFEnvironmentRO.DEV_NULL)

def parentVS = rs.getVertexState(dataTree.rootVertex) //yours change

def ids = parentVS.ids

//list of root id's

println " $ids"



//++++++++++++++++++++++++++++++++++++++  child table

def parentL = dataTree.rootVertex.edges.find {it.destination.entity.name == 'child_table'}
def protoEntity = parentL.destination.entity

def edpL = protoEntity.schema.dataProvider.getEntityDataProvider(protoEntity)

def rowsl = edpL.getRowCount(env)

println "child has $rowsl rows"



def rsl =  protoEntity.schema.dataProvider.getDefaultResultSet(dataTree, false, DFEnvironmentRO.DEV_NULL)

def parentVSl = rsl.getVertexState(parentL.destination)

def idsl = parentVSl.ids

println "array: $idsl"



//#######################################################################################################################





//change the number of records per list

lstidcnt = 50;



def i = 1, x = 0, bad = 0, y = 0;



List  listid = [];



 idsl.each { id ->

 println " $id"}

Executing Groovy script 'ListPart'



root has 805 rows

 [697, 698, 699]

child has 1712 rows

array: []



Script execution took 66 ms

Thanks!

I don't think so. The script is working perfectly for me. It looks that parent IDs 697,698,699 have no child records?

You are welcome!

Filip

no, child table has 1712 rows and 16 of them was shown when script was run...

There is also limitation that the script returns only those child IDs of the parents which are selected in a related view.
So the children depend on the selection of parents and are not for all the parents in resultset. You may select all parent rows in the table and then you got all IDs in child entity.

Also the child table and root table has to be directly related. Will not work if there is another e.g. 1:1 relationship between.

Does it explain the behaviour? 

Thank you, Filip

Igor, thanks for your question too!