Do a get with optional parameters in Golang

Question

I need to do an api where query using optional query string parameters.

I tried something like:

func (app *App) getFunctionarys(w http.ResponseWriter, r *http.Request) {
    v := r.URL.Query()

    Id := v.Get("Id")
    Name := v.Get("Name")
    Cpf := v.Get("Cpf")
    Role:= v.Get("Role")
    Status := v.Get("Status")

    dbdata := &Funcionary{}
    err := app.Database.QueryRow("SELECT Id,DataCad,Role,Cpf,Name,Uf,Salary,Status FROM `funcionary` WHERE Id = ? OR Name= ? OR Cpf = ? OR Role= ? OR Status = ?", Id, Name, Cpf, Role, Status).Scan(&dbdata.Id, &dbdata.DataCad, &dbdata.Role, &dbdata.Cpf, &dbdata.Name, &dbdata.Uf, &dbdata.Salary, &dbdata.Status)

    if err != nil {
        log.Println(err)
    }

    w.WriteHeader(http.StatusOK)
    if err := json.NewEncoder(w).Encode(dbdata); err != nil {
        panic(err)
    }
}

I have 5 records, but my api is only returning the first record, regardless of which query strings I put. How can I make an api that performs a Where based on the optional parameters received from the request?

Answer 1

Try using the Query function instead of the Queryrow function, Queryrow returns at most one element while Query returns all of the query result.

But the Query function does not return a pointer of Row but a pointer of Rows, then it is not possible to use the Scan function directly to read each line of the query result. You can loop to read the result using the method Next of Rows.

In your case the code would be something like this:

rows, err := app.Database.Query("SELECT Id,DataCad,Role,Cpf,Name,Uf,Salary,Status FROM `funcionary` WHERE Id = ? OR Name= ? OR Cpf = ? OR Role= ? OR Status = ?", Id, Name, Cpf, Role, Status)

var dbdata []*Funcionary

for rows.Next() {
    funcionary := &Funcionary{}
    err := rows.Scan(&funcionary.Id, &funcionary.DataCad, &funcionary.Role, &funcionary.Cpf, &funcionary.Name, &funcionary.Uf, &funcionary.Salary, &funcionary.Status)
    if err != nil {
      // tratar o erro como vc achar adequado
      log.Fatal(err)
    }
    dbdata = append(dbdata, funcionary)
  }

If you are interested to learn more about the function: official documentation of the Query function.