Complex query in GORM involving MySQL functions cos, radians

Question

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

Hi,

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

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.

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?

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

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
}

Forgot to credit the question : http://stackoverflow.com/questions/1006654/fastest-way-to-find-distance-between-two-lat-long-points for the fantastic MySQL query. — Robin M, Feb 04 '16 at 09:17

score 0 · Answer 1 · answered Feb 04 '16 at 12:50

0

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

Criteria Builder

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

lt('distance', 25)

answered Feb 04 '16 at 12:50

tylerwal

1,858
2
15
20

Amazing, I'll give that a try. The figures 51.519159 and -0.133190 are actually the latitude and longitude which will change each time the query is executed as it's based upon the user's location. Does that affect me using the formula as you describe? – Robin M Feb 04 '16 at 13:56
Unfortunately, it would. I don't believe there is any way for the `formula` to receive parameters. – tylerwal Feb 04 '16 at 14:14
1

Considering the SQL needs to be malleable, I think your best bet would be the `sqlRestriction` criterion method, which goes within your `list` closure of the criteria builder. – tylerwal Feb 04 '16 at 14:59

Complex query in GORM involving MySQL functions cos, radians

1 Answers1