Take the 2-minute tour ×

I m doing something like this to get the data...

 def prods = Product.executeQuery("select category.id,category.name, avg(competition1Price), avg(competition2Price), avg(onlineCompetitionPrice) from Product group by category.id")
        render prods as JSON

Not the output I'm getting is this..

        [[1,"Colchones y",1657.4784,2071.5,1242.5]]

these are just the values..

I want to use the same query and get key value pair.. like the way you do using findAll(query) But I can't seem to implement this query using findAll()

Please Help


share|improve this question
add comment

1 Answer

That's because the instances of prods are just objects with the result of your query and not instances of Product or other domain class (you used HQL that not represents a domain class). You can:

  • consider using a view for this query and mapping as a domain class;
  • manually build the output;

The second option is something like (not tested):

  def output = [[:]]
  prods.each { result ->
    def prod = ['category.id' : result[0] , 'category.name': result[1]] //and so on...
    output << prod
  render output as JSON

Using this second option, to change the result produced, just cange the structure of your map. You can also have a list of maps if needed.

share|improve this answer
and how to use a vie for this query and mapping as a domain class?? –  ashishashen Sep 14 '12 at 13:34
Do you know how to create a database view? The mapping is pretty the same of a table. –  Sérgio Michels Sep 14 '12 at 13:52
No ..can you please provide some links .. and the 2nd option worked fine. but it's still not in the format I need it. I need it something like.. { rows: [ { "id": "22", "CategoryName": "spurTree13", "CategoryRole": "Profit Driver1", "PrimaryObjective": "Increase Volume1",},{so on...}]} –  ashishashen Sep 14 '12 at 14:46
add comment

Your Answer


