title: Golang GORM query locations around a position with Lat and Lng
body: <p>I'm trying to make a specific request with <a href="http://gorm.io" rel="noreferrer">GORM</a>.</p>
  
  <p>Here are the tables I use:</p>
  
  <pre><code>+-------------------+
  | Tables            |
  +-------------------+
  | locations         |
  | shops             |
  | shops_tags        |
  | tags              |
  +-------------------+
  </code></pre>
  
  <p><em>Locations table</em></p>
  
  <pre><code>+------------+------------------+------+-----+---------+----------------+
  | 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    |                |
  +------------+------------------+------+-----+---------+----------------+
  </code></pre>
  
  <p><em>Location model</em></p>
  
  <pre><code>type Location struct {
      gorm.Model
      ShopID int64
      Shop   Shop
      Lat    float64 `gorm:"type:decimal(10,8)"`
      Lng    float64 `gorm:"type:decimal(11,8)"`
  }
  </code></pre>
  
  <p><em>Shops table</em></p>
  
  <pre><code>+-------------+------------------+------+-----+---------+----------------+
  | 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       |                |
  +-------------+------------------+------+-----+---------+----------------+
  </code></pre>
  
  <p><em>Shop model</em></p>
  
  <pre><code>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"`
  }
  </code></pre>
  
  <p><em>Tag model</em></p>
  
  <pre><code>type Tag struct {
      gorm.Model
      Name  string  `json:"name" gorm:"type:varchar(180)"`    
      Shops []*Shop `json:"shops" gorm:"many2many:shops_tags;"`
  }
  </code></pre>
  
  <p><em>Handler to return query in JSON</em></p>
  
  <pre><code>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(&amp;shops)
  
      // Json
      return c.JSON(http.StatusOK, echo.Map{
          "shops": &amp;shops,
      })
  }
  </code></pre>
  
  <p>So we have <strong>Stores</strong>, which have <strong>Locations</strong>, we want to display <strong>Stores</strong> within 15km of our <strong>Location</strong>. Only the <strong>last location</strong> is useful, that's why, we limited the results to 1 in decreasing order.</p>
  
  <p><em>Edit</em></p>
  
  <p>Thanks to <a href="https://stackoverflow.com/users/1766831/rick-james">@Rick James</a> for suggesting me to rephrase my question, here is the query in <code>MYSQL</code>, how to adapt it to <code>go-gorm</code>:</p>
  
  <pre><code>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 &gt;= '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)
                              )
                          )
                      )
                  )
              )
          )
      ) &lt; '500'
      AND shops.id = (locations.shop_id)
  )
  WHERE
    shops.active = 1
  GROUP BY
    shops.id
  ORDER BY
    distance ASC
  LIMIT
    100
  </code></pre>