Combine lists of objects with no duplicates

Question

I have 2 groovy sql resultset, I need to combine the result set so that project_no should be unique and case_no can have multiple elements if there is a duplicate project_no

Below are the 2 groovy sql resultset

[[project_no:0-10001,case_no:00492268],[project_no:0-10160,case_no:01957580],[project_no:1-10014,case_no:02022686]]

[[project_no:0-10160,case_no:01957590],[project_no:1-10014,case_no:019126],[project_no:1-2896337,case_no:02039596]]

Desired List

[[project_no:0-10001,case_nos:[00492268]], [project_no:0-10160,case_nos:[01957580,01957590]] ,[project_no:1-10014,case_nos:[02022686,019126]], [project_no:1-2896337,case_nos:[02039596]]]

This is what I have tried

caseResultForAnalysis.each { ca ->
    def ptmp = [:], caseList = []
    tempPrList.add(ca["project_no"])
    ptmp["project_no"] = ca["project_no"]
    caseList.add(ca["case_no"])
    if (caseList.size() > 0) {
        ptmp["case_nos"] = caseList
        mergedCaseResult.push(ptmp)
    }
}
mergedCaseResult.each { ma ->
    def ptmp = [:], caseList = []
    caseResultForUploads.each { cp ->
        if (!tempPrList.contains(cp["project_no"])) {
            ptmp["project_no"] = ma["project_no"]
            caseList.add(cp["case_no"])
        } else if (ma["project_no"] == cp["project_no"]) {
            //if (!ma["case_nos"].contains(cp["case_no"]))
            List tmp = ma["case_nos"]
            if (!tmp.contains(cp["case_no"]))
                ma["case_nos"].add(cp["case_no"])
        }
    }
    if (caseList.size() > 0) {
        ptmp["case_nos"] = caseList
        mergedCaseResult.push(ptmp)
    }
}

//1st list caseResultForAnalysis
//2nd List caseResultForUploads
//desired List mergedCaseResult

Is there a better way to do this, for better readability and less resource consumption?

Answer 1

Formulate your SQL query such that you get exactly the combined results you want. Don't use Groovy to do work that the database should be doing for you. It will be faster and easier.

Answer 2

To provide a Groovy related answer: There is a method groupBy() on Iterables, which is one of my favourites. You could

• merge the lists into one list, for example using collectMany
• groupBy the project_no of your maps
• for each project_no transform the list of maps into a single list containing case_nos.

In Groovy, this is a three-liner:

List list1 = [[project_no:"0-10001",case_no:"00492268"],[project_no:"0-10160",case_no:"01957580"],[project_no:"1-10014",case_no:"02022686"]]
List list2 = [[project_no:"0-10160",case_no:"01957590"],[project_no:"1-10014",case_no:"019126"],[project_no:"1-2896337",case_no:"02039596"]]

List desiredResult = [[project_no:"0-10001",case_nos:["00492268"]], [project_no:"0-10160",case_nos:["01957580","01957590"]] ,[project_no:"1-10014",case_nos:["02022686","019126"]], [project_no:"1-2896337",case_nos:["02039596"]]]

List result = [list1, list2].collectMany{ it }
        .groupBy{ it.project_no }
        .collect { project_no, maps -> [project_no: project_no, case_nos: maps.case_no ] }

assert desiredResult == result