Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Deleted user
0 / 0 points
Next:
badges earned

Your Points Tracker
Challenges
Leaderboard
  • Global
  • Feed

Badge for your thoughts?

You're enrolled in our new beta rewards program. Join our group to get the inside scoop and share your feedback.

Join group
Recognition
Give the gift of kudos
You have 0 kudos available to give
Who do you want to recognize?
Why do you want to recognize them?
Kudos
Great job appreciating your peers!
Check back soon to give more kudos.

Past Kudos Given
No kudos given
You haven't given any kudos yet. Share the love above and you'll see it here.

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

REST Endpoint + Behaviours Edited

Hi!
I have a Postgres DB with three tables: cities, regions and countries. I can get a list of cities of the country through the regions table.
When creating issue, I have 2 fields, Country and City.
When I select a country in the Country field, I need to get the entire list of cities of this country.
With the help of REST Endpoint (two REST Endpoints) and Behaviors (also two Behaviours) everything works for me, except that in the City field the whole list of cities is pulled up without further search by city. Searching by country works without problems. For example, if I write two letters "AU" in the Country field, then all countries that have "AU" are pulled up. In the case of cities, such a thing does not work. Help understand why.

Here is how I get Countries with the ability to “search” (REST Endpoint + Behaviors):

@BaseScript CustomEndpointDelegate delegate
getCountries(httpMethod: "GET") { MultivaluedMap queryParams->
def queryY = queryParams.getFirst("query") as String
def rt = [:]

// ----- > There should be data about the connection <----------

try {
sql
def rows = sql.rows("""select btc.name_en as country
FROM btrips_country btc WHERE btc.name_en ILIKE ?.C order by btc.name_en"""
, [C:"%${queryY}%".toString()])
rt = [
items : rows.collect { GroovyRowResult row ->
[
value: row.get("country"),
html: row.get("country"),
label: row.get("country"),
]
},
total: rows.size(),
footer: "Choose destination Country "
]
} finally {
sql.close()
conn.close()
}
return Response.ok(new JsonBuilder(rt).toString()).build();
}


And Behaviour (in Initialization block):

//customfield_13431 == Country cf
getFieldById("customfield_13431").convertToSingleSelect([
ajaxOptions: [
url : getBaseUrl() + "/rest/scriptrunner/latest/custom/getCountries",
query: true,
formatResponse: "general"
]
])

 

And here is part of how I try to get the cities of the selected country, but the “search” does not work:

@BaseScript CustomEndpointDelegate delegate
getCitiesOfCountry(httpMethod: "GET") { MultivaluedMap queryParams->

def queryY = queryParams.getFirst("country") as String
def queryX = queryParams.getFirst("city") as String
def rt = [:]

// ----- > There should be data about the connection <----------

try {
sql

def rows = sql.rows("""SELECT ct.name_en as city
FROM btrips_city ct
JOIN (
SELECT rg.id as id, rg.name_en as name_en
FROM btrips_region rg
WHERE
rg.country_id =
(SELECT id FROM btrips_country WHERE name_en = $queryY)
) rgs
ON ct.region_id = rgs.id

order by ct.name_en"""//, [C:"%${queryX}%".toString()]
)
// where ct.name_en ILIKE ?.C - this part must be include before Order by!
rt = [
items : rows.collect { GroovyRowResult row ->
[
value: row.get("city"),
html: row.get("city"),
label: row.get("city"),
]
},
total: rows.size(),
getCountry: queryY,
getCity: queryX,
footer: "Choose destination City "
]
} finally {
sql.close()
conn.close()
}
return Response.ok(new JsonBuilder(rt).toString()).build();
}

And Behaviour (in Fields block):

String ccountry = getFieldById("customfield_13431").getValue() //Country customfield
String ccity = getFieldById("customfield_13432").getValue() //City customfield

getFieldById("customfield_13432").convertToSingleSelect([ //City customfield
ajaxOptions: [
url : getBaseUrl() + "/rest/scriptrunner/latest/custom/getCitiesOfCountry?country=$ccountry&city=$ccity",
query: true,
formatResponse: "general"
]
])

So, for work with City field I don't understand, how I must modify second REST Endpoint and second Behaviour, so that the search (when I type some letters) in the City field works as well as in the Country field?

1 answer

0 votes

With query:true, the behavior will automatically append &query=[letters typed by user] to the rest API call.

You can't change that name on the rest endpoint.

So change 

def queryX = queryParams.getFirst("city") as String

To

def queryX = queryParams.getFirst("query") as String

BUT, I have never tried to pass additional parameters to the url in addition to query:true. So if that doesn't work, you may want to use URI parameters instead of query parameters. Like this:

url : "/rest/scriptrunner/latest/custom/getCitiesOfCountry/$country

(btw, if your rest endpoint is on the same server, you don't need the base url)

In the rest endpoint definition, you'll need to read that path:

@BaseScript CustomEndpointDelegate delegate
getCitiesOfCountry(httpMethod: "GET") { MultivaluedMap queryParams, String body, HttpServletRequest request ->
def extraPath = getAdditionalPath(request) //this method is included with scriptrunner and returns a strring starting with /. So '/selectedCountry' in your case
def queryY = ''
if(extraPath){
queryY= extraPath.split('/')[1]
}
def queryX = queryParams.getFirst("query") as String
...

Hi! Thanks for your response.
These methods did not help me or I did something wrong(
but I found a way out as follows:
I modified the code a bit in REST Endpoint:

def query = queryParams.getFirst("query") as String    

String country = queryParams.getFirst("country") as String

And I split the sql part into two requests instead of one like this:

def regionId = sql.rows("""SELECT rg.id FROM btrips_region rg WHERE rg.country_id = 
(SELECT id FROM btrips_country WHERE name_en=${country})""")
String regions = regionId.collect{it[0]}       
String sqlL = "SELECT ct.name_en as city FROM btrips_city ct WHERE ct.region_id in (" +regions[1..-2]+ ") and ct.name_en ILIKE ?.C order by ct.name_en"
def rows = sql.rows(sqlL, [C:"${query}%".toString()])

and in Behaviours (for cities of country):

url : getBaseUrl() + "/rest/scriptrunner/latest/custom/getCitiesOfCountry?country="+ccountry

and it works

Like Graziella Iezzi likes this

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase

The benefits of using Jira in different departments

Jira is a great tool to use across different departments. Forget that paperwork – switch to Jira and get that tasks done smoothly. Marketing Jira allows for a complete digital transformation of you...

97 views 0 5
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you