// ** 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
}
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
}