grails: converting SQL into domain classes

Question

I am developing a GRAILS application (I'm new to GRAILS and inherited the project from a previous developer). I'm slowly getting a small grasp for how GRAILS operates and the use of DOMAIN classes, hibernate etc. The MySQL db is hosted on Amazon and we're using ElasticCache.

Do any of you more knowledgeable folks know how I can go about converting the following SQL statement into domain classes and query criteria.

if(params?.searchterm) {

    def searchTerms = params.searchterm.trim().split( ',' )
    def resultLimit = params.resultlimit?: 1000
    def addDomain = ''

    if (params?.domainname){
        addDomain = " and url like '%${params.domainname}%' "
    }

    def theSearchTermsSQL = ""

    /*
     *  create c.name rlike condition for each search term
     *
     */
    searchTerms.each{
        aSearchTerm -> 
            if( theSearchTermsSQL != '' ){
                theSearchTermsSQL += ' or '
            }
            theSearchTermsSQL += "cname rlike '[[:<:]]" + aSearchTerm.trim() +  "[[:>:]]'"
    }

    /*
     *  build query
     *
     */
    def getUrlsQuery = "select
        u.url as url,
        c.name as cname,
        t.weight as tweight
    from
       (category c, target t, url_meta_data u )
    where
       (" + theSearchTermsSQL + ")
    and 
        t.category_id = c.id
        and t.url_meta_data_id = u.id
        and u.ugc_flag != 1 " + addDomain + "
    order by tweight desc
    limit " + resultLimit.toLong()


    /* 
     *  run query
     *
     */
    Sql sqlInstance = new Sql( dataSource )

    def resultsList = sqlInstance.rows( getUrlsQuery )

}

The tables are as follows (dummy data):

[Category]
id | name
-----------
 1 | small car
 2 | bike
 3 | truck
 4 | train
 5 | plane
 6 | large car
 7 | caravan

[Target]
id | cid | weight | url_meta_data_id
----------------------------------------
 1 |  1  |  56    |        1
 2 |  1  |  76    |        2
 3 |  3  |  34    |        3
 4 |  2  |  98    |        4
 5 |  1  |  11    |        5
 6 |  3  |  31    |        7
 7 |  5  |  12    |        8
 8 |  4  |  82    |        6

[url_meta_data]
id | url                          | ugc_flag
---------------------------------------------
 1 | http://www.example.com/foo/1 |    0
 2 | http://www.example.com/foo/2 |    0
 3 | http://www.example.com/foo/3 |    1
 4 | http://www.example.com/foo/4 |    0
 5 | http://www.example.com/foo/5 |    1
 6 | http://www.example.com/foo/6 |    1
 7 | http://www.example.com/foo/7 |    1
 8 | http://www.example.com/foo/8 |    0

domain classes

class Category {

    static hasMany = [targets: Target]

    static mapping = {
        cache  true
        cache usage: 'read-only'
        targetConditions cache : true
    }

    String name

    String source
}

class Target {

    static belongsTo = [urlMetaData: UrlMetaData, category: Category]
    static mapping = {
        cache true
        cache usage: 'read-only'
    }

    int weight

}

class UrlMetaData {

    String url

    String ugcFlag

    static hasMany = [targets: Target ]

    static mapping = { 
        cache true
        cache usage: 'read-only'
    }

    static transients = ['domainName']

    String getDomainName() {

        return HostnameHelper.getBaseDomain(url)
    }
}

Basically, a url from url_meta_data can be associated to many categories. So in essence what I'm trying to achieve should be a relatively basic operation...to return all the urls for the search-term 'car', their weight(i.e importance) and where the ugc_flag is not 1(i.e the url is not user-generated content). There are 100K + of records in the db and these are imported from a third-party provider. Note that all the URLs do belong to my client - not doing anything dodgy here.

Note the rlike I've used in the query - I was originally using ilike %searchterm% but that would find categories where searchterm is part of a larger word, for example 'caravan') - unfortunately though the rlike is not going to return anything if the user requests 'cars'.

I edited the code - as Igor pointed out the strange inclusion originally of 'domainName'. This is an optional parameter passed that allows the user to filter for urls of only a certain domain (e.g. 'example.com')

Answer 1

I'd create an empty list of given domain objects, loop over the resultsList, construct a domain object from each row and add it to a list of those objects. Then return that list from controller to view. Is that what you're looking for?

Answer 2

1) If it's a Grails application developed from a scratch (rather than based on a legacy database structure) then you probably should already have domain classes Category, Target, UrlMetaData (otherwise you'll have to create them manually or with db-reverse-engineer plugin)

2) I assume Target has a field Category category and Category has a field UrlMetaData urlMetaData

3) The way to go is probably http://grails.org/doc/2.1.0/ref/Domain%20Classes/createCriteria.html and I'll try to outline the basics for your particular case

4) Not sure what theDomain means - might be a code smell, as well as accepting rlike arguments from the client side

5) The following code hasn't been tested at all - in particular I'm not sure how disjunction inside of a nested criteria works or not. But this might be suitable a starting point; logging sql queries should help with making it work ( How to log SQL statements in Grails )

def c = Target.createCriteria() //create criteria on Target
def resultsList = c.list(max: resultLimit.toLong()) { //list all matched entities up to resultLimit results
    category { //nested criteria for category
        //the following 'if' statement and its body is plain Groovy code rather than part of DSL that translates to Hibernate Criteria
        if (searchTerms) { //do the following only if searchTerms list is not empty
            or { // one of several conditions
                for (st in searchTerms) { // not a part of DSL - plain Groovy loop
                    rlike('name', st.trim())) //add a disjunction element
                }
            }
        }
        urlMetaData { //nested criteria for metadata
            ne('ugcFlag', 1) //ugcFlag not equal 1
        }
    }    
    order('weight', 'desc') //order by weight
}

Possibly the or restriction works better when written explicitly

if (searchTerms) { 
    def r = Restrictions.disjunction() 
    for (st in searchTerms) {
        r.add(new LikeExpression('name', st.trim()))
    }
    instance.add(r) //'instance' is an injected property
}

Cheers,
Igor Sinev