Grails - Query list of items from multiple domains

Question

Working on grails 3 application. My domain structure is defined below:

Job{
Integer id
String title   
Date publishedDate
}

JobType{
Integer id
String name

}

JobJobTypeMap{
  String jobId
  String jobTypeId
}

For example,

**Job**
id title
1  job1
2  job2

**JobType**
id name
 1 govt
 2 private

**JobJobTypeMap**
jobId jobTypeId
1      1
1      2
2      2

I need to get list of jobs (offset and max attributes and order published date descending) with particular jobType.

Kindly, don't ask me to change the domain structure.

Any suggestions would be appreciated

Where is mongodb involved ? Please ad some code that you have tried to get answers. — Sachin, Apr 11 '16 at 15:38

score 1 · Answer 1 · answered Apr 13 '16 at 20:38

You are not defining properly the domain classes. You don't have to create the relation class between Job and JobType, Grails will do it automatically on you DB.

Job{
Integer id
String title   
Date publishedDate
JobType jobType
}

JobType{
Integer id
String name

}

List<Job> jobs = Job.findAllByJobType(jobTypeInstance, [sort: '', order:'', max: '', offset: ''])

score 1 · Answer 2 · edited May 23 '17 at 12:07

You have to use Grails SQL/HSQL to query the DB.

In controller

def getList() {

    def params = [column:"job.title", order:'DESC', offset:0, limit:5]

    Sql sql = new Sql(dataSource)
    String query = """
        SELECT  job_job_type_map.job_id jobId, job_job_type_map.job_type_id jobTypeId,
                job.title jobTitle, jobType.name jobTypeName
        FROM    job job, job_type jobType, job_job_type_map job_job_type_map
        WHERE   job_job_type_map.job_id = job.id
        AND     job_job_type_map.job_type_id = jobType.id
        ORDER BY ${params.column} ${params.order}
        LIMIT   :limit
        OFFSET  :offset
    """

    List<GroovyRowResult> result  = sql.rows(query, params)
    result.each {
        println "${it.jobId} ${it.jobTypeId} ${it.jobTitle} ${it.jobTypeName}"
    }

    render template: 'list', model: [jobRows:result]
}

in GSP

<g:each in="${jobRows}" var="job">
    "${job.jobId} ${job.jobTypeId} ${job.jobTitle} ${job.jobTypeName}" <br/>
</g:each>

Be careful ORDER BY ${params.column} ${params.order} There is restriction in using named parameter in some places. You can find here

Enjoy!

Also you can see some more solutions on https://github.com/mdaynul/jobproject4StackOverflow with HSQL and class biniding. — devbd, Apr 19 '16 at 22:47

score 0 · Answer 3 · answered Apr 21 '16 at 09:17

If you do not want to change domain structure and do not want to write SQL/HSQL then you can use this approach:

def jobTypeId = JobType.findByName(params.jobTypeName).id
def jobIdList = JobJobTypeMap.findAllByJobTypeId(jobTypeId)?.jobId
def jobList = Job.createCriteria().list(max:max, offset:offset) {
  'in'("id", jobIdList)
  order("publishedDate", "desc")
}

jobList will give you the list of all jobs with specific job type, max, offset and order by publishedDate.

Grails - Query list of items from multiple domains

3 Answers3