Grails/groovy/GORM performing inner-join like query

Question

I am trying to get a list from 3 domains in grails (something like inner join in any regular programming language)

here are my domains

class Category{
    Integer id
    String name  
}
class Tag{
    Integer id
    String name  
}
class Content{
    Integer id
    Category category
    Tag tag
    String text
}
//--------
def contentInstance = Content.findAllWhere(id:id.toInteger())

I want Content.text, Category.name and Tag.name in a list to be able to show it in View Thank you

Answer 1

You can use criteria query api to select custom columns. You can use like:

Content.withCriteria {
projections {
  property('text')
    category {
      property('name')
    }
    tag{
      property('name')
    }
  }       
}

or you can create alias like:

Content.withCriteria {
createAlias("category","categoryAlias")
createAlias("tag","tagAlias")
projections {
  property('text')
  property('categoryAlias.name')
  property('tagAlias.name')

} 
and{
    eq('category.id','categoryAlias.id')
    eq('tag.id','tagAlias.id')
  }    
}

Hope you got the idea...