Hello. I am currently trying to piece together a way of getting the results from one mysql query, adding them to a list, then iterating through these results in another query using Groovy.
My code is as follows:
I am a bit unsure where to go from here, this does not appear to work, I'm pretty sure the it.queryResults is wrong, but I need to fill this specific bit of the query with each of the results of the previous query in turn.
I also need the list items to appear with quotation marks around them in order for the second query to work, which I am not sure can happen like this.
Any suggestions would be really appreciated.
This looks like a job for SQL GROUP BY. Nesting related SQL queries like this is an anti-pattern, because it usually means you're trying to implement some kind of join or aggregation that would be much more efficient in SQL.
Put Groovy aside for now and open up your database's SQL shell. Then work out how to join your ticket and fields tables in a single query. Finally, modify the query to use GROUP BY and COUNT to produce the list of ticket counts grouped by field. Once you've got the query working in your SQL interpreter, you can put it into your Groovy program.
Use the right tool for the job - and SQL is the right tool for this job.