GORM中涉及MySQL函数cos，弧度的复杂查询

| Grails Version: 3.0.9 | Groovy Version: 2.4.5 | JVM Version: 1.8.0_60

| Grails版本：3.0.9 | Groovy版本：2.4.5 | JVM版本：1.8.0_60

Hi,

嗨，

I have the following GORM query involving a join between the 'Event' and 'EventCategory' domain objects with page results.

我有以下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
}

However, I need to add in / merge the following MySQL query that calculates the distance of each event (in miles) from the supplied latitute and longitude (51.519159, -0.133190) and then filters out any event that is in excess of 25 miles (in the example). The events are also ordered by distance from the supplied lat/long.

但是，我需要添加/合并以下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;

My question is how to best approach changing the GORM query to incorporate the distance calulations?

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

Do I need to throw out my GORM query and resort to a native HQL query? I'm hoping not.

我是否需要抛弃GORM查询并使用本机HQL查询？我希望不是。

Any thoughts would be greatly appreciated.

任何想法将不胜感激。

I'll include relevant parts of the two domain objects for completeness.

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

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
}

1 个解决方案

#1

You can store the distance query internally within a domain class as a formula and then call upon it as if it were a property of that class.

您可以将内部距离查询作为公式存储在域类中，然后将其作为该类的属性进行调用。

Event class:

活动类：

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))))
    """
}

You may need to experiment with the formula's string (whether the new line character will cause a problem). It also seems like several parts of the query are constants and could be factored out: acos(cos(radians(51.519159)), radians(-0.133190), sin(radians(51.519159))

您可能需要尝试公式的字符串（新行字符是否会导致问题）。看起来好像查询的几个部分都是常量，可以用掉：acos（cos（弧度（51.519159）），弧度（-0.133190），sin（弧度（51.519159））

Criteria Builder

Criteria Builder

You can use the distance property now like any other property:

您可以像任何其他属性一样使用距离属性：

lt('distance', 25)

#1