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?
