Beispiel #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
}
Beispiel #2
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
}