Complex Search Across Grails Domain Classes

I want to be able to do complex searches on grails objects. This is presently implemented using stored procedures on my site that build up a SQL query and parses all of those resul

Solution 1:

I've never tried the searchable plugin so may be selling it short. Your best bet is probably HQL queries or Hibernate criteria builder. I like HQL for complex queries since it's similar to SQL. For a blog post comparing the use of these technologies from Grails see http://blog.xebia.com/2008/06/04/querying-associations-in-grails-with-hql-criteria-and-hibernatecriteriabuilder/ For the HQL reference see http://docs.jboss.org/hibernate/core/3.3/reference/en/html/queryhql.html For Hibernate criteria see http://docs.jboss.org/hibernate/core/3.3/reference/en/html/querycriteria.html

Solution 2:

You could also have a look at the FilterPane plugin to help you (or the user) to build these queries. There is a GUI that can handle most of such questions, or you could resort to add the FilterPane fields yourself in the HTML and controller if you prefer to have the control over the queries.

FilterPane then translates the fields to Hibernate criteria builder internally.

Solution 3:

All of the queries you have listed can be accomplished with the searchable plugin.

I believe you CAN do the queries you have suggested with HQL but Compass/Lucene is really the better tool for that solution

Solution 4:

In my experience HQL queries were the only solution for complex queries.

Some queries even made it necessary to use non-Hibernate functions of the underlying DB, e.g. setting dialect = "org.hibernate.dialect.ExtendedMySqlDialect" in DataSource.groovy and then implementing something like this:

package org.hibernate.dialect;

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

publicclassExtendedMySqlDialectextendsMySQL5InnoDBDialect {
    publicExtendedMySqlDialect() {
        registerFunction("timeStampAdd", newSQLFunctionTemplate(Hibernate.TIMESTAMP, "TIMESTAMPADD(?1, ?2, ?3)"));
        registerFunction("timeStampDiff", newSQLFunctionTemplate(Hibernate.INTEGER, "TIMESTAMPDIFF(?1, ?2, ?3)"));
}

The above functions might also help you when dealing with dates.