// ** What if instead of building the query here, we prepare a statement in the constructor and this just calls that? Any advantage?
func (d *databaseService) Find(id int) (models.SalaryData, error) {
// Hit the DB and Scan in one go.
var s models.SalaryData
var stack string
err := d.db.QueryRow(`
SELECT id, company, city, state, country, base, bonus, perks, date_added, IFNULL(group_concat(DISTINCT s.stack_name SEPARATOR ' '), '')
FROM code_salary AS c
INNER JOIN salary_stack AS s
WHERE c.id = s.salarydata_id
AND c.id = ?
GROUP BY c.id
`, id).Scan(&s.Id, &s.Company, &s.City, &s.State, &s.Country, &s.Base, &s.Bonus, &s.Perks, &s.DateAdded, &stack)
if err != nil {
return s, err
}
// Converting stack (string returned from the db join) to a proper
// slice to be stored in the models.salaryData type. If we weren't attached
// to mysql, this wouldn't have to be so clumsy.
if stack != "" {
s.Stack = strings.Split(stack, " ")
}
return s, err
}
// curl -XPOST -H 'Content-Type: application/json' -d '{"company": "Google", "city": "New York", "state": "New York", "Country": "USA", "Base": 136000, "Bonus": "20%", "Perks": 20000, "Stack": ["linux", "mysql", "apache", "php"]}' http://localhost:3000/api/salaryData
// SalaryDataCreate handler.
func (c *salaryDataController) SalaryDataCreate(w http.ResponseWriter, r *http.Request) (error, int) {
// Create a new SalaryData struct and set the DateAdded.
s := models.SalaryData{}
s.DateAdded = time.Now()
// hah, err := ioutil.ReadAll(r.Body)
// if err != nil {
// fmt.Printf("%s", err)
// }
// fmt.Printf("%s", hah)
// Decode the JSON onto the struct.
err := json.NewDecoder(r.Body).Decode(&s)
if err != nil {
return err, http.StatusInternalServerError
}
// * Is there any validation we should do here?
// Create the item via the DB Service.
err = c.databaseService.Create(s)
if err != nil {
return err, http.StatusInternalServerError
}
// Get and return all the salaries after creation.
// * Should set the 20 here as config for the default number to show
ss, err := c.databaseService.FindN(20, "", false)
if err != nil {
return err, http.StatusNotFound
}
// Marshal the documents as JSON.
json, err := json.Marshal(ss)
if err != nil {
return err, http.StatusInternalServerError
}
// Write the JSON to the response.
w.Header().Set("Content-Type", "application/json")
w.Write(json)
return nil, http.StatusCreated
}
func (d *databaseService) FindN(n int, sort string, asc bool) ([]models.SalaryData, error) {
// Set n to be 1000 when no limit is specified.
if n == 0 {
n = 1000
}
// Validate the sort field.
qFields := map[string]bool{
"company": true,
"city": true,
"state": true,
"country": true,
"base": true,
"bonus": true,
"perks": true,
"dateAdded": true,
}
if ok := qFields[sort]; !ok {
sort = "null"
}
// Just in case there's some sql injection voodoo, let's double check 'sort'.
valid, err := regexp.Compile("^[A-Za-z0-9_]+$")
if err != nil {
return nil, err
}
if !valid.MatchString(sort) {
return nil, errors.New("invalid sort query parameter")
}
// Convert the asc bool to the corresponding string.
var ascStr string
if asc {
ascStr = "ASC"
} else {
ascStr = "DESC"
}
// Construct the SQL query.
query := fmt.Sprintf(`
SELECT id, company, city, base, bonus, perks, date_added, IFNULL(group_concat(DISTINCT s.stack_name SEPARATOR ','), '')
FROM code_salary AS c
INNER JOIN salary_stack AS s
WHERE c.id = s.salarydata_id
GROUP BY c.id
ORDER BY %s %s
LIMIT ?
`, sort, ascStr)
// Hit the DB.
rows, err := d.db.Query(query, n)
if err != nil {
return nil, err
}
defer rows.Close()
// Scan the rows and populate the 'ss' slice.
var ss []models.SalaryData
for rows.Next() {
var s models.SalaryData
var stack string
err := rows.Scan(&s.Id, &s.Company, &s.City, &s.Base, &s.Bonus, &s.Perks, &s.DateAdded, &stack)
if err != nil {
return nil, err
}
if stack != "" {
s.Stack = strings.Split(stack, ",")
}
ss = append(ss, s)
}
err = rows.Err()
if err != nil {
return nil, err
}
return ss, nil
}