Vithun's Avatar Vithun's Blog

Combining MySQL Full-Text & Spatial Search In Grails

In the Grails project I’m currently working on, we were required to have a search functionality which would take into account, among other things, location data (spatial query) and keywords (full-text query). For this we required our Hibernate queries to support the relevant features. This would be possible by using an appropriate MySQLDialect. For spatial queries, we could use this dialect: org.hibernatespatial.mysql.MySQLSpatialDialect. However, we also wanted Hibernate to support full-text queries. So I extended this dialect and wrote a custom dialect for our application. In this case, our custom dialect looked something like this:

1
2
3
4
5
6
7
8
9
10
11
12

package org.philevents;

import org.hibernate.Hibernate;
import org.hibernate.dialect.function.SQLFunctionTemplate;

public class MySQLDialect extends org.hibernatespatial.mysql.MySQLSpatialDialect {
    public MySQLDialect() {
        super();
        registerFunction("match_against",
            new SQLFunctionTemplate(Hibernate.BOOLEAN, "match ?1 against (?2 in boolean mode)") );
    }
}

We now have a match_against function available in Hibernate. This takes two arguments (represented by ?1 and ?2) and performs the full-text search using MySQL’s match...against.

Next, we have to make sure that our Grails application uses the above dialect class. This is done by configuring the dialect of the dataSource in /grails-app/conf/DataSource.groovy. Since I was using different data sources for different environments, my configuration looked something like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

environments {
    development {
        dataSource {
            ...
            username = "..."
            password = "..."
            dialect = org.philevents.MySQLDialect
        }
    }
    test {
        dataSource {
            ...
            username = "..."
            password = "..."
            dialect = org.philevents.MySQLDialect
        }
    }
    production {
        dataSource {
            ...
            username = "..."
            password = "..."
            dialect = org.philevents.MySQLDialect
        }
    }
}

Now a full-text query can be executed by something like this: Let us assume we have a Book domain class which has a field called abstract. We now have to search this field based on some given keywords. This is done by:

1

def results = Book.executeQuery("from Book b where match_against((abstract), ?) <> 0", [keywords])

Spatial queries can be executed by the features provided by the HibernateSpatialDialect class, and are available in our application as our custom dialect is a subset of it. Details of how we used spatial queries in our application will be provided in a future post.

Reply on X

Related Posts