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.

• George Willcox: Is this really a proper answer? Currently, it seems better placed as a comment. I feel as if you should give more information about how to make the changes to the program.
• 200_success: @t3chb0t I stand by this answer. The author asked if there is a "better way to do this". My answer is that it is silly to do this in Groovy, when it can be done in SQL. This code needs to be thrown away completely. I can't provide the SQL code, though, without knowing what the schema looks like.
• t3chb0t: @200_success You're right. Sorry. I guess I voted to hastily... let me put it right with +1.

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