mysql - GORM 中涉及 MySQL 函数 cos、弧度的复杂查询

Question

| Grails 版本:3.0.9|常规版本:2.4.5| JVM版本:1.8.0_60

你好，

我有以下 GORM 查询，涉及“Event”和“EventCategory”域对象与页面结果之间的连接。

def advancedSearchWithPagedResults(int max, int offset, String search, Date startDate, Date endDate, List myEventCategories) {

    // need to convert our list of ints to longs as the field they'll be compared to is a long
    def listOfLongs = myEventCategories.collect {
        it.toLong()
    }

    def wildcardSearch = search ? '%' + search + '%' : '%'

    def ids = Event.createCriteria().list {

        projections {
            distinct 'id'
        }

        maxResults max
        firstResult offset

        or {
            like("eventName", wildcardSearch)
            like("address.town", wildcardSearch)
            like("address.county", wildcardSearch)
        }
        and {
            if (listOfLongs) {
                eventCategories {
                    'in'("id", listOfLongs)
                }
            }
            between("startDateTime", startDate, endDate)
            eq("enabled", true)
        }
        order("startDateTime", "asc")
    }

    /* Get the acual events using the list of id's just obtained */
    def results = Event.getAll(ids)

    return results
}

但是，我需要添加/合并以下 MySQL 查询，该查询计算每个事件与提供的纬度和经度 (51.519159, -0.133190) 的距离(以英里为单位)，然后过滤掉超过的任何事件25 英里(示例中)。事件也按与提供的纬度/经度的距离排序。

SELECT 
  `event`.*, 
   ( 3959 * acos( cos( radians(51.519159) ) * cos( radians( `event`.address_latitude ) ) 
   * cos( radians(`event`.address_longitude) - radians(-0.133190)) + sin(radians(51.519159)) 
   * sin( radians(`event`.address_latitude)))) AS distance 
FROM `event` 
WHERE `event`.enabled = 1 
HAVING distance < 25 
ORDER BY distance;

我的问题是如何最好地改变 GORM 查询以合并距离计算？

我是否需要放弃我的 GORM 查询并求助于 native HQL 查询？我希望不会。

如有任何想法，我们将不胜感激。

为了完整性，我将包括两个领域对象的相关部分。

class Event implements Taggable {

    static hasMany = [courses:          Course,
                      eventCategories:  EventCategory,
                      faqs:             FAQ]

    static belongsTo = [eventOrganiser:     EventOrganiser]

    java.util.Date dateCreated
    java.util.Date lastUpdated
    boolean enabled = true
    String eventName
    String organisersDescription
    @BindingFormat('dd/MM/yyyy HH:mm')
    java.util.Date startDateTime
    @BindingFormat('dd/MM/yyyy HH:mm')
    java.util.Date endDateTime
    @BindingFormat('dd/MM/yyyy HH:mm')
    java.util.Date entriesOpenDateTime
    @BindingFormat('dd/MM/yyyy HH:mm')
    java.util.Date entriesCloseDateTime
    BigDecimal fromPrice
    Address address
    Contact primaryContact

    static embedded = ['address','primaryContact']

    // transient fields
    double distanceFromUsersPostcode
    ....
}

class EventCategory {

    static hasMany = [events:Event]
    static belongsTo = [Event]

    String parentCategoryName
    String parentSubCategoryName
    String categoryName
    String description

    int order
}

Answer 1

您可以在域类内部将距离查询存储为 formula然后像调用该类的属性一样调用它。

事件类:

static mapping = {
    distance formula: """
        (3959 * acos(cos(radians(51.519159)) * 
        cos(radians(ADDRESS_LATITUDE)) * cos( radians(ADDRESS_LONGITUDE) - 
        radians(-0.133190)) + sin(radians(51.519159)) * sin( radians(ADDRESS_LATITUDE))))
    """
}

您可能需要对公式的字符串进行试验(换行符是否会导致问题)。查询的几个部分似乎也是常量，可以分解出来:acos(cos(radians(51.519159)), radians(-0.133190), sin(弧度(51.519159))

条件生成器

您现在可以像使用任何其他属性一样使用 distance 属性:

lt('distance', 25)