Example #1
0
// ** 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
}
Example #2
0
// 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
}
Example #3
0
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
}