Order By Clause in GoLang and MySQL Database

Make sure Git is installed on your machine and in your system’s PATH. Install the package to your $GOPATH with the go tool from shell:

$ go get github.com/go-sql-driver/mysql

Create a database with the name is learngodb. This database have 1 tables: Product table.

--
-- Table structure for table `product`
--

CREATE TABLE `product` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(250) NOT NULL,
  `price` double NOT NULL,
  `quantity` int(11) NOT NULL,
  `status` tinyint(1) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `product`
--

INSERT INTO `product` (`id`, `name`, `price`, `quantity`, `status`) VALUES
(1, 'Tivi 1', 200, 4, 1),
(2, 'Tivi 2', 500, 6, 0),
(3, 'Laptop 1', 250, 11, 1),
(4, 'Laptop 2', 241, 6, 0),
(5, 'Laptop 3', 600, 11, 0),
(6, 'Mobile 1', 120, 20, 1),
(7, 'Mobile 2', 210, 25, 0);




Create new folder named src. In src folder, create new folder named entities. In this folder, create new file named product_entity.go as below:

package entities

type Product struct {
	Id       int64
	Name     string
	Price    float32
	Quantity int
	Status   bool
}

In src folder, create new folder named config. In this folder, create new file named config.go, this file is used to connect mysql database:

package config

import (
	"database/sql"
	_ "github.com/go-sql-driver/mysql"
)

func GetMySQLDB() (db *sql.DB, err error) {
	dbDriver := "mysql"
	dbUser := "root"
	dbPass := "123456"
	dbName := "learngodb"
	db, err = sql.Open(dbDriver, dbUser + ":" + dbPass + "@/" + dbName)
	return
}




In src folder, create new folder named models. In models folder, create new file named product_model.go. This file contains methods to interact with the database.

package models

import (
	"database/sql"
	"entities"
)

type ProductModel struct {
	Db *sql.DB
}

func (productModel ProductModel) OrderByASC() ([]entities.Product, error) {
	rows, err := productModel.Db.Query("select * from product order by price asc")
	if err != nil {
		return nil, err
	} else {
		products := []entities.Product{}
		for rows.Next() {
			var id int64
			var name string
			var price float32
			var quantity int
			var status bool
			err2 := rows.Scan(&id, &name, &price, &quantity, &status)
			if err2 != nil {
				return nil, err2
			} else {
				product := entities.Product{id, name, price, quantity, status}
				products = append(products, product)
			}
		}
		return products, nil
	}
}

func (productModel ProductModel) OrderByDESC() ([]entities.Product, error) {
	rows, err := productModel.Db.Query("select * from product order by price desc")
	if err != nil {
		return nil, err
	} else {
		products := []entities.Product{}
		for rows.Next() {
			var id int64
			var name string
			var price float32
			var quantity int
			var status bool
			err2 := rows.Scan(&id, &name, &price, &quantity, &status)
			if err2 != nil {
				return nil, err2
			} else {
				product := entities.Product{id, name, price, quantity, status}
				products = append(products, product)
			}
		}
		return products, nil
	}
}




In src folder, create new file named main.go as below:

package main

import (
	"config"
	"fmt"
	"models"
)

func main() {

	fmt.Println("Sort by Price Ascending")
	SortPriceAscending()

	fmt.Println("Sort by Price Descending")
	SortPriceDescending()

}

func SortPriceAscending() {
	db, err := config.GetMySQLDB()
	if err != nil {
		fmt.Println(err)
	} else {
		productModel := models.ProductModel{
			Db: db,
		}
		products, err2 := productModel.OrderByASC()
		if err2 != nil {
			fmt.Println(err2)
		} else {
			for _, product := range products {
				fmt.Println("Id:", product.Id)
				fmt.Println("Name:", product.Name)
				fmt.Println("Price:", product.Price)
				fmt.Println("Quantity:", product.Quantity)
				fmt.Println("Status:", product.Status)
				fmt.Println("----------------------------")
			}
		}
	}
}

func SortPriceDescending() {
	db, err := config.GetMySQLDB()
	if err != nil {
		fmt.Println(err)
	} else {
		productModel := models.ProductModel{
			Db: db,
		}
		products, err2 := productModel.OrderByDESC()
		if err2 != nil {
			fmt.Println(err2)
		} else {
			for _, product := range products {
				fmt.Println("Id:", product.Id)
				fmt.Println("Name:", product.Name)
				fmt.Println("Price:", product.Price)
				fmt.Println("Quantity:", product.Quantity)
				fmt.Println("Status:", product.Status)
				fmt.Println("----------------------------")
			}
		}
	}
}




Sort by Price Ascending
Id: 6
Name: Mobile 1
Price: 120
Quantity: 20
Status: true
----------------------------
Id: 1
Name: Tivi 1
Price: 200
Quantity: 4
Status: true
----------------------------
Id: 7
Name: Mobile 2
Price: 210
Quantity: 25
Status: false
----------------------------
Id: 4
Name: Laptop 2
Price: 241
Quantity: 6
Status: false
----------------------------
Id: 3
Name: Laptop 1
Price: 250
Quantity: 11
Status: true
----------------------------
Id: 2
Name: Tivi 2
Price: 500
Quantity: 6
Status: false
----------------------------
Id: 5
Name: Laptop 3
Price: 600
Quantity: 11
Status: false
----------------------------

Sort by Price Descending
Id: 5
Name: Laptop 3
Price: 600
Quantity: 11
Status: false
----------------------------
Id: 2
Name: Tivi 2
Price: 500
Quantity: 6
Status: false
----------------------------
Id: 3
Name: Laptop 1
Price: 250
Quantity: 11
Status: true
----------------------------
Id: 4
Name: Laptop 2
Price: 241
Quantity: 6
Status: false
----------------------------
Id: 7
Name: Mobile 2
Price: 210
Quantity: 25
Status: false
----------------------------
Id: 1
Name: Tivi 1
Price: 200
Quantity: 4
Status: true
----------------------------
Id: 6
Name: Mobile 1
Price: 120
Quantity: 20
Status: true
----------------------------