I am using this library from http://jmoiron.github.io/sqlx/ to do a query. Following the documentation led me to the code below.
func cities(w http.ResponseWriter, r *http.Request, _ httprouter.Params) error {
var main string
var secondary string
var limit string
queryParams := make(map[string]interface{})
if k := r.PostFormValue("keyword"); k != "" {
main = "city.name LIKE :keyword"
queryParams["keyword"] = k + "%"
}
if sk := r.PostFormValue("secondaryKeyword"); sk != "" && sk != "null" {
secondary = "OR city.name = :secondaryKeyword"
queryParams["secondaryKeyword"] = sk
}
if mr := r.PostFormValue("maxResults"); mr != "" {
limit = "LIMIT :maxResults"
queryParams["maxResults"] = mr
}
if lr := r.PostFormValue("lastRequest"); lr != "" && lr == "1" {
limit = ""
}
query := fmt.Sprintf(`
SELECT
city.geonameid AS cityId,
city.name AS cityName,
COALESCE(admin1.name_local, '') AS admin1Name,
country.name AS countryName,
CONCAT_WS(' ', city.name, city.asciiname, country.name) AS searchString
FROM geonames_cities1000 AS city
INNER JOIN geonames_countryinfo AS country
ON city.iso_alpha2 = country.iso_alpha2
LEFT OUTER JOIN geonames_admin1_codes_ascii as admin1
ON admin1.code = CONCAT(city.iso_alpha2, '.', city.admin1_code)
WHERE %s %s
ORDER BY city.name ASC %s;
`, main, secondary, limit)
nstmt, err := sql.DB.PrepareNamed(query)
if err != nil {
return err
}
rows, err := nstmt.Queryx(queryParams)
if err != nil {
return err
}
results := []interface{}{}
for rows.Next() {
row := make(map[string]interface{})
err := rows.MapScan(row)
if err != nil {
return err
}
results = append(results, row)
}
b, err := json.Marshal(results)
if err != nil {
return err
}
w.Write(b)
return nil
}
Sending a POST request postman chrome plugin with these values:
keyword: "tron"
maxResults: 7
lastRequest: 0
gave this JSON output:
[
{
"admin1Name": "VXR0YXJhZGl0",
"cityId": 1605268,
"cityName": "VHJvbg==",
"countryName": "VGhhaWxhbmQ=",
"searchString": "VHJvbiBUcm9uIFRoYWlsYW5k"
},
{
"admin1Name": "Q2FsYWJyaWE=",
"cityId": 8949073,
"cityName": "VHJvbmNh",
"countryName": "SXRhbHk=",
"searchString": "VHJvbmNhIFRyb25jYSBJdGFseQ=="
},
{
"admin1Name": "QXJhZ29u",
"cityId": 3107444,
"cityName": "VHJvbmNow7Nu",
"countryName": "U3BhaW4=",
"searchString": "VHJvbmNow7NuIFRyb25jaG9uIFNwYWlu"
},
{
"admin1Name": "UHVlYmxh",
"cityId": 8859151,
"cityName": "VHJvbmNvbmFs",
"countryName": "TWV4aWNv",
"searchString": "VHJvbmNvbmFsIFRyb25jb25hbCBNZXhpY28="
},
{
"admin1Name": "U2NobGVzd2lnLUhvbHN0ZWlu",
"cityId": 2821000,
"cityName": "VHLDtm5kZWw=",
"countryName": "R2VybWFueQ==",
"searchString": "VHLDtm5kZWwgVHJvbmRlbCBHZXJtYW55"
},
{
"admin1Name": "U8O4ci1UcsO4bmRlbGFn",
"cityId": 3133880,
"cityName": "VHJvbmRoZWlt",
"countryName": "Tm9yd2F5",
"searchString": "VHJvbmRoZWltIFRyb25kaGVpbSBOb3J3YXk="
},
{
"admin1Name": "VG9uZ3Nh",
"cityId": 1252408,
"cityName": "VHJvbmdzYQ==",
"countryName": "Qmh1dGFu",
"searchString": "VHJvbmdzYSBUcm9uZ3NhIEJodXRhbg=="
}
]
Why are the results like this? Putting the results in spew.Dump() will give something like this (I'm just outputting one of the rows):
(map[string]interface {}) (len=5) {
(string) (len=11) "countryName": ([]uint8) (len=6 cap=6) {
00000000 4e 6f 72 77 61 79 |Norway|
},
(string) (len=12) "searchString": ([]uint8) (len=26 cap=26) {
00000000 54 72 6f 6e 64 68 65 69 6d 20 54 72 6f 6e 64 68 |Trondheim Trondh|
00000010 65 69 6d 20 4e 6f 72 77 61 79 |eim Norway|
},
(string) (len=6) "cityId": (int64) 3133880,
(string) (len=8) "cityName": ([]uint8) (len=9 cap=9) {
00000000 54 72 6f 6e 64 68 65 69 6d |Trondheim|
},
(string) (len=10) "admin1Name": ([]uint8) (len=15 cap=15) {
00000000 53 c3 b8 72 2d 54 72 c3 b8 6e 64 65 6c 61 67 |S..r-Tr..ndelag|
}
What do I do wrong here?'
EDIT:
I tried doing as Elwinar suggested with structs instead. But something really weird is going on.
This code works:
type City struct {
AdminName string `json:"admin1Name" db:"admin1Name"`
CityID int64 `json:"cityId" db:"cityId"`
CityName string `json:"cityName" db:"cityName"`
CountryName string `json:"countryName" db:"countryName"`
SearchString string `json:"searchString" db:"searchString"`
}
But this code does not work and outputs error "missing destination name cityId":
type City struct {
CityId int64 `json:"cityId" db:"cityId"`
CityName string `json:"cityName" db:"cityName"`
Admin1Name string `json:"admin1Name" db:"admin1Name"`
CountryName string `json:"countryName" db:"countryName"`
SearchString string `json:"searchString" db:"searchString"`
}
What is the difference?
Solution: Must separate the tags with space key. Can not do spacing with tab key, and can not use commas to separate tags.