REST Endpoint + Behaviours

[Vitalii]

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?

[PD Sheehan]

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
...

[Vitalii]

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

[Rodolfo So]

Hi @Vitalii 

Can you share the complete rest endpoint script? I would like to test this scenario.Thanks