Menu
I'm trying to make a specific request with GORM.
Here are the tables I use:
+-------------------+
| Tables |
+-------------------+
| locations |
| shops |
| shops_tags |
| tags |
+-------------------+
Locations table
+------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| shop_id | bigint(20) | YES | | NULL | |
| lat | decimal(10,8) | YES | | NULL | |
| lng | decimal(11,8) | YES | | NULL | |
+------------+------------------+------+-----+---------+----------------+
Location model
type Location struct {
gorm.Model
ShopID int64
Shop Shop
Lat float64 `gorm:"type:decimal(10,8)"`
Lng float64 `gorm:"type:decimal(11,8)"`
}
Shops table
+-------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(180) | YES | UNI | NULL | |
| active | tinyint(1) | YES | | 1 | |
+-------------+------------------+------+-----+---------+----------------+
Shop model
type Shop struct {
gorm.Model
Name string `json:"name" gorm:"type:varchar(180);unique_index"`
Active int `json:"active" gorm:"type:tinyint(1);default:1"`
Tags []*Tag `json:"tags" gorm:"many2many:shops_tags;"`
Locations []Location `json:"locations" gorm:"locations"`
}
Tag model
type Tag struct {
gorm.Model
Name string `json:"name" gorm:"type:varchar(180)"`
Shops []*Shop `json:"shops" gorm:"many2many:shops_tags;"`
}
Handler to return query in JSON
func GetShops(c echo.Context) error {
db := db.DbManager()
// Get POST data (name lat and lng)
type geo struct {
Lat string `json:"lat" form:"lat" query:"lat"`
Lng string `json:"lng" form:"lng" query:"lng"`
}
// Bind request body
g := new(geo)
if err := c.Bind(g); err != nil {
return c.JSON(http.StatusForbidden, err)
}
shops := []model.Shop{}
// Totally disordered attempt of a query with `go-gorm` to display the list of nearby shops, sorted by the distance between us.
db.Preload("Locations", func(db *gorm.DB) *gorm.DB {
// How here use g.Lat and g.Lng for this request
return db.Order("locations.id DESC").Limit(1)
}).Preload("Tag").Find(&shops)
// Json
return c.JSON(http.StatusOK, echo.Map{
"shops": &shops,
})
}
So we have Stores, which have Locations, we want to display Stores within 15km of our Location. Only the last location is useful, that's why, we limited the results to 1 in decreasing order.
Edit
Thanks to @Rick James for suggesting me to rephrase my question, here is the query in MYSQL, how to adapt it to go-gorm:
SELECT
shops.id,
shops.name, (
(
6371.04 * ACOS(
(
(
COS(
(
(
PI() / 2
) - RADIANS(
(90 - locations.lat)
)
)
) * COS(
PI() / 2 - RADIANS(90 - -33.73788500)
) * COS(
(
RADIANS(locations.lng) - RADIANS('151.23526000')
)
)
) + (
SIN(
(
(
PI() / 2
) - RADIANS(
(90 - locations.lat)
)
)
) * SIN(
(
(
PI() / 2
) - RADIANS(90 - -33.73788500)
)
)
)
)
)
)
) AS 'distance',
locations.id AS 'location_id',
locations.shop_id,
locations.lat,
locations.lng,
locations.created_at
FROM
shops
INNER JOIN locations ON(
locations.created_at >= '2017-12-13'
AND(
(
6371.04 * ACOS(
(
(
COS(
(
(
PI() / 2
) - RADIANS(
(90 - locations.lat)
)
)
) * COS(
PI() / 2 - RADIANS(90 - -33.73788500)
) * COS(
(
RADIANS(locations.lng) - RADIANS('151.23526000')
)
)
) + (
SIN(
(
(
PI() / 2
) - RADIANS(
(90 - locations.lat)
)
)
) * SIN(
(
(
PI() / 2
) - RADIANS(90 - -33.73788500)
)
)
)
)
)
)
) < '500'
AND shops.id = (locations.shop_id)
)
WHERE
shops.active = 1
GROUP BY
shops.id
ORDER BY
distance ASC
LIMIT
100
292
Looking at the query I would definitely hand craft this one and not rely on any ORM to execute the query. Refer to using the Exec method on a gorm database instance ref.
But... To use an ORM like gorm, using gorm's SQL-Builder is a possibility. Here is an example that should be of guidance. Please note that this is for SQLite Database, and not for MYSQL so the functions in use do not exist. We are mainly concerned with building the query.
package main
import (
"fmt"
"time"
"github.com/jinzhu/gorm"
_ "github.com/jinzhu/gorm/dialects/sqlite"
)
const (
layoutISO = "2006-01-02"
)
type Shop struct {
gorm.Model
Name string `json:"name" gorm:"type:varchar(180);unique_index"`
Active int `json:"active" gorm:"type:tinyint(1);default:1"`
Tags []Tag `json:"tags" gorm:"many2many:shops_tags;"`
Locations []Location `json:"locations" gorm:"locations"`
}
type Tag struct {
gorm.Model
Name string `json:"name" gorm:"type:varchar(180)"`
Shops []Shop `json:"shops" gorm:"many2many:shops_tags;"`
}
type Location struct {
gorm.Model
ShopID int64
Shop Shop
Lat float64 `gorm:"type:decimal(10,8)"`
Lng float64 `gorm:"type:decimal(11,8)"`
}
// Get POST data (name lat and lng)
type geo struct {
Lat float64 `json:"lat" form:"lat" query:"lat"`
Lng float64 `json:"lng" form:"lng" query:"lng"`
}
var distance_calculation = `
(
(
6371.04 * ACOS(((COS(((PI() / 2) - RADIANS((90 - locations.lat)))) *
COS(PI() / 2 - RADIANS(90 - ?)) *
COS((RADIANS(locations.lng) - RADIANS(?))))
+ (SIN(((PI() / 2) - RADIANS((90 - locations.lat)))) *
SIN(((PI() / 2) - RADIANS(90 - ?))))))
)
)`
func main() {
db, err := gorm.Open("sqlite3", "test.db")
if err != nil {
panic("failed to connect database")
}
defer db.Close()
// Enable Logger, show detailed log
db.LogMode(true)
// Migrate the schema
db.AutoMigrate(&Shop{})
db.AutoMigrate(&Tag{})
db.AutoMigrate(&Location{})
g := new(geo)
g.Lat = -33.73788500
g.Lng = 151.23526000
type Result struct {
ID uint
Name string
Distance int
LocationID uint
ShopID uint
Lat float64
Lng float64
CreatedAt time.Time
}
date, _ := time.Parse(layoutISO, "2017-12-13")
var t []Result
err = db.Table("Shops").
Select("shops.id, shops.name, locations.id AS 'location_id', locations.shop_id, locations.lat, locations.lng, locations.created_at, "+
distance_calculation+" as distance\n", g.Lat, g.Lng, g.Lat).
Joins("inner join locations on shops.id = locations.shop_id\n").
Where(&Shop{Active: 1}).
Where("locations.created_at >= CAST(strftime('%s', ?) AS INT) * 1000", date).
Where(distance_calculation + " < ?", g.Lat, g.Lng, g.Lat, 500).
Group("\nshops.id\n").
Order("locations.id DESC\n").
Limit(100).Scan(&t).Error
if err != nil {
panic(err)
}
fmt.Printf("%+v\n", t)
}
Query Built with SQL Builder
(C:/Users/fakename/source/stackoverflow/go/geo-go-gorm/main.go:97)
[2019-10-31 15:36:37] [1.00ms] SELECT shops.id, shops.name, locations.id AS 'location_id', locations.shop_id, locations.lat, locations.lng, locations.created_at,
(
(
6371.04 * ACOS(((COS(((PI() / 2) - RADIANS((90 - locations.lat)))) *
COS(PI() / 2 - RADIANS(90 - -33.737885)) *
COS((RADIANS(locations.lng) - RADIANS(151.23526))))
+ (SIN(((PI() / 2) - RADIANS((90 - locations.lat)))) *
SIN(((PI() / 2) - RADIANS(90 - -33.737885))))))
)
) as distance
FROM "Shops" inner join locations on shops.id = locations.shop_id
WHERE ("shops"."active" = 1) AND (locations.created_at >= CAST(strftime('%s', '2017-12-13 00:00:00') AS INT) * 1000) AND (
(
(
6371.04 * ACOS(((COS(((PI() / 2) - RADIANS((90 - locations.lat)))) *
COS(PI() / 2 - RADIANS(90 - -33.737885)) *
COS((RADIANS(locations.lng) - RADIANS(151.23526))))
+ (SIN(((PI() / 2) - RADIANS((90 - locations.lat)))) *
SIN(((PI() / 2) - RADIANS(90 - -33.737885))))))
)
) < 500
) GROUP BY shops.id
ORDER BY locations.id DESC
LIMIT 100
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
