我想列出StartAddress
(联接表Address
)周围10km半径内的races
我的模型就是这样写的:
type (
// Race model
Race struct {
Base // ID, CreatedAt, UpdatedAt, DeletedAt
Title string `json:"title" gorm:"title;"`
StartAddress Address `json:"startaddress" gorm:"foreignKey:StartAddressID"`
StartAddressID string `json:"start_address_id" gorm:"type:char(36);start_address_id;"`
EndAddress Address `json:"endaddress" gorm:"foreignKey:EndAddressID"`
EndAddressID string `json:"end_address_id" gorm:"type:char(36);end_address_id;"`
StartDate time.Time `json:"start_date" gorm:"start_date;"`
EndDate time.Time `json:"end_date" gorm:"end_date;"`
}
Address struct {
Base // ID, CreatedAt, UpdatedAt, DeletedAt
Street string `json:"street" gorm:"street;"`
Zipcode string `json:"zipcode" gorm:"zipcode;"`
City string `json:"city" gorm:"city;"`
Lat float64 `json:"lat" gorm:"lat;"`
Lng float64 `json:"lng" gorm:"lng;"`
}
)
要查询我的数据库,我使用GORM v2
我不知道如何编写使用cube
&通过GORM与PostgreSQL进行earthdistance
扩展。
我已经添加了扩展:
CREATE EXTENSION cube;
和
CREATE EXTENSION earthdistance;
类似于此,但使用GORM语法,按距离排序:
SELECT * FROM races
INNER JOIN addresses ON addresses.id = races.start_address_id
AND earth_box(ll_to_earth(48.8589507,2.2770205), 5000) @> ll_to_earth(addresses.lat, addresses.lng)
ORDER BY races.status DESC
LIMIT 100
您在那里得到的查询可以在Gorm中完成,如下所示:
var res []Race
db.Model(&Race{}).
Joins("StartAddress").
Where("earth_box(ll_to_earth(?,?), ?) @> ll_to_earth(addresses.lat, addresses.lng)", ptLat, ptLng, dist).
Order("races.status DESC").
Limit(100).
Find(&res)
要按距离获得订单,您需要将Order(...).
替换为:
Clauses(clause.OrderBy{
Expression: clause.Expr{
SQL: "earth_distance(ll_to_earth(?,?), ll_to_earth(addresses.lat, addresses.lng)) ASC",
Vars: []interface{}{ptLat, ptLng},
WithoutParentheses: true,
},
}).