Querying GORM association

Question

I want to build very simple help desk application in purpose of learning grails. I want user to be able to login, create request/incident. User select productline, topic for that productline and subtopic for that topic. Every subtopic has one or many support users that are responsible for it. On the other side, support user can see list of the incidents that fall in his 'jurisdiction'. My domain classess look something like this:

class Request{  


Date dateCreated
String subject
String startedBy 
String description
String status
String priority 
Productline productline
Topic topic
Subtopic subtopic


static constraints = {
    //id unique: true
    dateCreated(blank:true,nullable:true)
    subject()
    description(maxSize:5000)
    status (blank:true,nullable:true)
    priority(inList:["Normalan","Hitno","Nije hitno"])
    productline(blank:true,nullable:true)
    topic(blank:true,nullable:true)
    subtopic(blank:true,nullable:true)
    company(blank:true,nullable:true)
    contact(blank:true,nullable:true)
    startedBy(blank:true,nullable:true)
    acceptedBy(blank:true,nullable:true)
}

}

class Subtopic {

String subtopic
Productline productline 
Topic topic


static hasMany = [responsibilities: Responsibility]


String toString(){
    "$subtopic"
}
static constraints = {
    subtopic()
    productline()
    topic()
}

List contacts(){
    return responsibilities.collect{it.contact}
}

List addToContacts(Contact contact){
    Responsibility.link(contacts, this)
    return contacts()
}

List removeFromContacts(Contact contact){
    Responsibility.unlink(contact, this)
    return contacts()
}

}

class Responsibility {

Contact contact
Subtopic subtopic

static Responsibility link(contact, subtopic){
    def r = Responsibility.findByContactAndSubtopic(contact, subtopic)
    if(!r){
        r = new Responsibility()
        contact?.addToResponsibilities(r)
        subtopic?.addToResponsibilities(r)
    }
    return r
}

static void unlink(contact, subtopic){
    def r = Responsibility.findByContactAndSubtopic(contact, subtopic)
    if(r){
        contact?.removeFromResponsibilities(r)
        subtopic?.removeFromResponsibilities(r)
        r.delete()
    }
}

static constraints = {
}

}

class User {

transient springSecurityService

String username
String password
boolean enabled
boolean accountExpired
boolean accountLocked
boolean passwordExpired

String toString(){
    "$username"
}


static constraints = {
    username blank: false, unique: true
    password blank: false
}

static mapping = {
    password column: '`password`'
}

Set<Role> getAuthorities() {
    UserRole.findAllByUser(this).collect { it.role } as Set
}

def beforeInsert() {
    encodePassword()
}

def beforeUpdate() {
    if (isDirty('password')) {
        encodePassword()
    }
}

protected void encodePassword() {
    password = springSecurityService.encodePassword(password)
}

}

class Contact {

String realname
String company
String mobile
String fix
String email
User user

String toString(){
    "$realname"
}

static hasMany = [responsibilities: Responsibility]

static constraints = {
}

List subtopics(){
    return responsibilities.collect{it.subtopic}
}

List addToSubtopics(Subtopic subtopic){
    Responsibility.link(this, subtopic)
    return subtopics()
}

List removeFromSubtopics(Subtopic subtopic){
    Responsibility.unlink(this, subtopic)
    return subtopics()
}

}

In my RequestController I have supportList() action. After successfull login users with role ROLE_SUPPORT are send to this action wich should filter data so the only request instances sent to view are the ones that the currently logged in user is responsible for. This is the action:

def supportList = {

    def contact = Contact.findByUser(springSecurityService.currentUser)
    def requestList = contact.subtopics().collect {Request.findAllBySubtopic(it)}

    params.max = Math.min(params.max ? params.int('max') : 10, 100)
    [requestInstanceList: requestList, requestInstanceTotal: requestList.count()]
}

I'm getting empty table. Actually, I'm getting expected number of rows but all fields are empty. What am I doing wrong?

This is my GSP:

<%@ page import="com.testapp.Request" %>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
        <meta name="layout" content="main" />
        <g:set var="entityName" value="${message(code: 'request.label', default: 'Request')}" />
        <title><g:message code="default.list.label" args="[entityName]" /></title>
    </head>
    <body>
        <div class="nav">
            <span class="menuButton"><g:link class="create" action="create">
                <g:message code="default.new.label" args="[entityName]" /></g:link></span>
        </div>
        <div class="body">
            <h1>Lista Zahteva</h1>
            <g:if test="${flash.message}">
            <div class="message">${flash.message}</div>
            </g:if>
            <div class="list">
                <table>
                    <thead>
                        <tr>

                            <g:sortableColumn property="id" 
                                title="${message(code:   'request.id.label', default: 'Id')}" />

                            <g:sortableColumn property="subject" title="Datum kreiranja" />

                            <g:sortableColumn property="subject" title="Tema" />

                            <g:sortableColumn property="priority" title="Prioritet" />

                            <th><g:message code="incident.startedBy" default="Započeo" /></th>

                            <g:sortableColumn property="status" title="Status" />

                        </tr>
                    </thead>
                    <tbody>
                    <g:each in="${requestInstanceList}" status="i" var="requestInstance">
                        <tr class="${(i % 2) == 0 ? 'odd' : 'even'}">

                            <td><g:link action="show" id="${requestInstance.id}">
                                ${fieldValue(bean: requestInstance, field: "id")}</g:link></td>

                            <td width="200">${fieldValue(bean:requestInstance,field:"dateCreated")}</td>

                            <td width="200">${fieldValue(bean: requestInstance, field: "subject")}</td>

                            <td width="200">${fieldValue(bean: requestInstance, field: "priority")}</td>

                            <td width="200">${fieldValue(bean: requestInstance, field: "startedBy")}</td>

                            <td width="200">${fieldValue(bean: requestInstance, field: "status")}</td>

                        </tr>
                    </g:each>
                    </tbody>
                </table>
            </div>
            <div class="paginateButtons">
                <g:paginate total="${requestInstanceTotal}" />
            </div>
        </div>
    </body>
</html>

Answer 1

With your current model, you can query current user responsibilities like this:

Responsability.findAllBySupport(springSecurityService.currentUser).
    collect {Incident.findAllBySubtopic(it.subtopic)}.flatten()

As it is, Responsability is just a join table between users and subtopics. If you removed it, and defined a many-to-many relationship between users and subtopics:

class User {
    static hasMany = [supportedSubtopics: Subtopic]
    ...
}

class SubTopic {
    static hasMany = [supportingUsers: User]
    ....
}

you could query like this:

springSecurityService.currentUser.supportedSubtopics.collect {Incidents.findBySubtopic(it)}.flatten()

By adding hasMany = [incidents: Incident] to Subtopic you could further simplify it to

springSecurityService.currentUser.supportedSubtopics*.incidents.flatten()