How to pass variables to query builder

[Prakash Ganeshan]

Hi,

I have a requirement:

In Validators in worflow transition I have to check this:

1.  In the transition screen, I ask for the Fix Versions and if the fix version is entered do the following:

Read the fixversions and see if the same fix version is assigned to any of the tickets in the same project.  To do this I am doing this script validator Groovy:

Problem here is I am retrieving the count but there are tickets which matches the condition.  I suspect problem is with fixVersion().in(fvname) in the below code.  Please help me on how to pass the fix versions to the query ?

Collection<Version> fixVersionList = issue.getFixVersions();
String fvname = "";
int i = 0;
if (fixVersionList.size() > 0)
{
 for (Version v : fixVersionList)
 {
 if( i == 0)
 {
 fvname = "\""+v.getName()+"\"";
 }
 else
 {
 fvname = fvname+"\",\""+"\""+v.getName()+"\"";
 }
 i = i + 1;
 }
 builder = JqlQueryBuilder.newBuilder();
 builder.where().project().eq("XXX").and().status().in("Open","In Progress").and().fixVersion().in(fvname);
 Query query = builder.buildQuery();
 SearchService searchService = ComponentManager.getInstance().getSearchService();
 SearchResults results = null;
 try
 {
 User user = ComponentAccessor.getJiraAuthenticationContext().getLoggedInUser();
 results = searchService.search(user, query, PagerFilter.getUnlimitedFilter());
 }
 catch (Exception e){}
 nCount = results.getTotal();

[Prakash Ganeshan]

Answering my own question:

Collection<Version> fixVersionList = issue.getFixVersions();
def int fvsize = fixVersionList.size();
if (fvsize >= 1){
def String CSVfvs = "";
Issue curissue = issue
curissuekey = curissue.getKey();

# Get all the associated fix version with in QUotes and comma separated
for (Version v : fixVersionList) {
fvname = v.getName();
CSVfvs = CSVfvs + "\"" + fvname + "\",";
}

# Remove trailing comma
if (CSVfvs != null && CSVfvs.length() > 0 && CSVfvs.charAt(CSVfvs.length() - 1) == ',') {
CSVfvs = CSVfvs.substring(0, CSVfvs.length() - 1);
}

def jqlQueryParser = ComponentAccessor.getComponent(JqlQueryParser)
def searchProvider = ComponentAccessor.getComponent(SearchProvider)
def issueManager = ComponentAccessor.getIssueManager()
def user = ComponentAccessor.getJiraAuthenticationContext().getUser()
def jql = "project = JRN AND key != " + curissuekey + " and fixVersion in (" + CSVfvs + ")"
def query = jqlQueryParser.parseQuery(""+ jql + "")
try {
def results = searchProvider.search(query, user, PagerFilter.getUnlimitedFilter())
def int noOfTickets = results.getTotal()
if(noOfTickets > 0) {
message = "In Try JQL = "+ jql + "result count = " + results.getTotal() + "Current Key" + curissuekey + "Fvnames = "+ CSVfvs
invalidInputException = new InvalidInputException(message);
}
}
catch (Exception e) {
invalidInputException = new InvalidInputException(e.getMessage());
}
}

[Prakash Ganeshan]

Answering my own question:

Collection<Version> fixVersionList = issue.getFixVersions();
def int fvsize = fixVersionList.size();
if (fvsize >= 1){
 def String CSVfvs = "";
 Issue curissue = issue
 curissuekey = curissue.getKey();
 
 # Get all the associated fix version with in QUotes and comma separated
 for (Version v : fixVersionList) {
 fvname = v.getName();
 CSVfvs = CSVfvs + "\"" + fvname + "\",";
 }
 
 # Remove trailing comma
 if (CSVfvs != null && CSVfvs.length() > 0 && CSVfvs.charAt(CSVfvs.length() - 1) == ',') {
 CSVfvs = CSVfvs.substring(0, CSVfvs.length() - 1);
 }

 def jqlQueryParser = ComponentAccessor.getComponent(JqlQueryParser)
 def searchProvider = ComponentAccessor.getComponent(SearchProvider)
 def issueManager = ComponentAccessor.getIssueManager()
 def user = ComponentAccessor.getJiraAuthenticationContext().getUser()
 def jql = "project = JRN AND key != " + curissuekey + " and fixVersion in (" + CSVfvs + ")"
 def query = jqlQueryParser.parseQuery(""+ jql + "")
 try {
 def results = searchProvider.search(query, user, PagerFilter.getUnlimitedFilter())
 def int noOfTickets = results.getTotal()
 if(noOfTickets > 0) {
 message = "In Try JQL = "+ jql + "result count = " + results.getTotal() + "Current Key" + curissuekey + "Fvnames = "+ CSVfvs
 invalidInputException = new InvalidInputException(message);
 }
 }
 catch (Exception e) {
 invalidInputException = new InvalidInputException(e.getMessage());
 }
}

[Prakash Ganeshan]

Answering my own question:

Collection<Version> fixVersionList = issue.getFixVersions();
def int fvsize = fixVersionList.size();
if (fvsize >= 1){
 def String CSVfvs = "";
 Issue curissue = issue
 curissuekey = curissue.getKey();
 
 # Get all the associated fix version with in QUotes and comma separated
 for (Version v : fixVersionList) {
 fvname = v.getName();
 CSVfvs = CSVfvs + "\"" + fvname + "\",";
 }
 
 # Remove trailing comma
 if (CSVfvs != null && CSVfvs.length() > 0 && CSVfvs.charAt(CSVfvs.length() - 1) == ',') {
 CSVfvs = CSVfvs.substring(0, CSVfvs.length() - 1);
 }

 def jqlQueryParser = ComponentAccessor.getComponent(JqlQueryParser)
 def searchProvider = ComponentAccessor.getComponent(SearchProvider)
 def issueManager = ComponentAccessor.getIssueManager()
 def user = ComponentAccessor.getJiraAuthenticationContext().getUser()
 def jql = "project = JRN AND key != " + curissuekey + " and fixVersion in (" + CSVfvs + ")"
 def query = jqlQueryParser.parseQuery(""+ jql + "")
 try {
 def results = searchProvider.search(query, user, PagerFilter.getUnlimitedFilter())
 def int noOfTickets = results.getTotal()
 if(noOfTickets > 0) {
 message = "In Try JQL = "+ jql + "result count = " + results.getTotal() + "Current Key" + curissuekey + "Fvnames = "+ CSVfvs
 invalidInputException = new InvalidInputException(message);
 }
 }
 catch (Exception e) {
 invalidInputException = new InvalidInputException(e.getMessage());
 }
}

[JamieA]

We can help you write that, but can you just add a few words on why you want this? What happens if the fix version is used on another issue? What if it's not?

[Prakash Ganeshan]

Hi Jamie, it is our process here that one fix version can not be assigned to multiple tickets.  So want to ensure that the no two tickets has the same fixversion.

[Prakash Ganeshan]

Also I am looking at your example code passing JQL here https://scriptrunner.adaptavist.com/latest/jira/recipes/misc/running-a-jql-query.html

Also can you please let us know how can I pass my variables in the groovy script to JQL?

I want to go which ever is the best approach.