How do I write a createCriteria in grails which pull only few columns from the table instead of all columns?

Question

How do I write a createCriteria in grails which pull only few columns from the table instead of all columns?

I have a table called Ads. I want to retrieve only columns "Title" , "Price" and "Photo".

def c = Classified.createCriteria()
    def records = c.list {
            eq('publish_date', '2014-06-06')
        }
        maxResults(8)
    }

Above query retrieves all the records. How to restrict to only few columns?

Answer 1

You can use projections to achieve this - at the simplest

projections {
    property('title')
    property('price')
    property('photo')
}

would cause c.list to return a list of three-element lists, where records[n][0] is the title, records[n][1] is the price etc. If you want to be able to access the properties by name rather than by number then you need to assign aliases and use a result transformer

import org.hibernate.transform.AliasToEntityMapResultTransformer

def c = Classified.createCriteria()
def records = c.list {
    eq('publish_date', '2014-06-06')
    maxResults(8)
    projections {
        // first param is the property name, second is the alias definition -
        // typically you'd leave them the same but here I make them different
        // for demonstration purposes
        property('title', 'ttl')
        property('price', 'cost')
        property('photo', 'picture')
    }
    resultTransformer(AliasToEntityMapResultTransformer.INSTANCE)
}

Now records will be a list of maps rather than a list of lists, and you can access the projected properties by alias name - records[n].ttl, records[n].cost, etc.