Saving object having many-to-many relationship in grails

Question

I am trying to save objects having Many-Many Relationship . A SellingCompany can have many Accounts and an Account can be associated with many SellingCompanies. So there is a many-many relationship between the tables stored in SellingCompaniesAccount

My Account_Info domain is as follows:

class AccountInfo { 
    static mapping ={
        table 'AccountInfo'
        version false
        //id column:'accountInfoID'
    }

    String evi_pass_phrase
    String evi_username
    String security_key

    // to make sure fields show up in a particular order

    static constraints = {
        //accountInfoID(insert:false,update:false)
        evi_pass_phrase()
        evi_username()
        security_key()

    }

    static hasMany = [sellingcompaniesaccount:SellingCompaniesAccount]


    String toString() {
        return "${evi_username}"
    }
}

My SellingComapanies domain is as follows:

class SellingCompanies 
{

    static mapping = {  
        table 'SellingCompanies'
        version false
    }

    String name

    //static belongsTo = AccountInfo

    //static hasMany = [accounts: AccountInfo]
    static hasMany = [sellingcompaniesaccount:SellingCompaniesAccount]

    static constraints = {

    name(blank:false, validator:
        { val, obj ->
            def similarSellingCompanies = SellingCompanies.findByNameIlike(val)
            return !similarSellingCompanies || (obj.id == similarSellingCompanies.id)
        })
    }

    //String toString() { name }
}

The table that holds the Many-Many relationship is as follows:

class SellingCompaniesAccount {

    static constraints = {
        // ensure the group of sellingCompaneis and accountInfo values are unique
        agency_name(unique:['sellingCompanies','accountInfo'])
    }

    int agency_id
    String agency_name
    String consultant_id
    String code
    Boolean isActive
    String iata

    ContactInfo contactinfo

    static belongsTo = [sellingCompanies:SellingCompanies, accountInfo:AccountInfo]

        }

}

The form in the create.gsp file contains the code that actually iterates over all the different SellingCompanies and displays as a check-box.

<g:form action="save" method="post">
    <div class="dialog">
    <table width="500px" border="0px" color="red">
        <tbody>

            <tr class="prop">
                <td valign="top" class="name"><label for="accountInfo"><g:message
                    code="sellingCompaniesAccount.accountInfo.label"
                    default="Account Info" /></label></td>
                <td valign="top"
                    class="value ${hasErrors(bean: sellingCompaniesAccountInstance, field: 'accountInfo', 'errors')}">
                <g:select name="accountInfo.id"
                    from="${content_hub_admin.AccountInfo.list()}" optionKey="id"
                    value="${sellingCompaniesAccountInstance?.accountInfo?.id}" /></td>
            </tr>

            <tr class="prop">
                <td valign="top" class="name"><label for="sellingCompanies"><g:message
                    code="sellingCompaniesAccount.sellingCompanies.label"
                    default="Selling Companies" /></label></td>
                <td valign="top"
                    class="">
                    <g:each in="${content_hub_admin.SellingCompanies.list()}" var="item" status="i">
                        ${++i}.  ${item.name}&nbsp;&nbsp;<g:checkBox name="sellingcompanies_${++i-1}" optionKey="id" value="${item.id}" /> <br>
                    </g:each>
                <!--  end here by rsheyeah -->
                </td>
            </tr>



            <tr class="prop">
                <td valign="top" class="name"><label for="code"><g:message
                    code="sellingCompaniesAccount.code.label" default="Code" /></label></td>
                <td valign="top"
                    class="value ${hasErrors(bean: sellingCompaniesAccountInstance, field: 'code', 'errors')}">
                <g:textField name="code"
                    value="${sellingCompaniesAccountInstance?.code}" /></td>
            </tr>

            <tr class="prop">
                <td valign="top" class="name"><label for="agency_name"><g:message
                    code="sellingCompaniesAccount.agency_name.label"
                    default="Agencyname" /></label></td>
                <td valign="top"
                    class="value ${hasErrors(bean: sellingCompaniesAccountInstance, field: 'agency_name', 'errors')}">
                <g:textField name="agency_name"
                    value="${sellingCompaniesAccountInstance?.agency_name}" /></td>
            </tr>

            <tr class="prop">
                <td valign="top" class="name"><label for="isActive"><g:message
                    code="sellingCompaniesAccount.isActive.label" default="Is Active" /></label>
                </td>
                <td valign="top"
                    class="value ${hasErrors(bean: sellingCompaniesAccountInstance, field: 'isActive', 'errors')}">
                <g:checkBox name="isActive"
                    value="${sellingCompaniesAccountInstance?.isActive}" /></td>
            </tr>

            <tr class="prop">
                <td valign="top" class="name"><label for="agency_id"><g:message
                    code="sellingCompaniesAccount.agency_id.label" default="Agencyid" /></label>
                </td>
                <td valign="top"
                    class="value ${hasErrors(bean: sellingCompaniesAccountInstance, field: 'agency_id', 'errors')}">
                <g:textField name="agency_id"
                    value="${fieldValue(bean: sellingCompaniesAccountInstance, field: 'agency_id')}" />
                </td>
            </tr>

            <tr class="prop">
                <td valign="top" class="name"><label for="iata"><g:message
                    code="sellingCompaniesAccount.iata.label" default="Iata" /></label></td>
                <td valign="top"
                    class="value ${hasErrors(bean: sellingCompaniesAccountInstance, field: 'iata', 'errors')}">
                <g:textField name="iata"
                    value="${sellingCompaniesAccountInstance?.iata}" /></td>
            </tr>

            <tr class="prop">
                <td valign="top" class="name"><label for="consultant_id"><g:message
                    code="sellingCompaniesAccount.consultant_id.label"
                    default="Consultantid" /></label></td>
                <td valign="top"
                    class="value ${hasErrors(bean: sellingCompaniesAccountInstance, field: 'consultant_id', 'errors')}">
                <g:textField name="consultant_id"
                    value="${sellingCompaniesAccountInstance?.consultant_id}" /></td>
            </tr>

            <tr class="prop">
                <td valign="top" class="name"><label for="contactinfo"><g:message
                    code="sellingCompaniesAccount.contactinfo.label"
                    default="Contactinfo" /></label></td>
                <td valign="top"
                    class="value ${hasErrors(bean: sellingCompaniesAccountInstance, field: 'contactinfo', 'errors')}">
                <g:select name="contactinfo.id"
                    from="${content_hub_admin.ContactInfo.list()}" optionKey="id"
                    value="${sellingCompaniesAccountInstance?.contactinfo?.id}" /></td>
            </tr>

        </tbody>
    </table>
    </div>
    <div class="buttons"><span class="button"><g:submitButton
        name="create" class="save"
        value="${message(code: 'default.button.create.label', default: 'Create')}" /></span>
    </div>
</g:form>

Lastly the controller which handles the save and list functions.

class SellingCompaniesAccountController {

    private static Logger log = Logger.getLogger(SellingCompaniesAccountController.class)

    //def index = { }
    //def scaffold = true

    def index = { redirect(action:list,params:params) }

    //To limit access to controller actions based on the HTTP request method.
    def allowedMethods = [save:'POST']

    //create.gsp exists
    def create = {
        render(view:"create")
    }

    //edit.gsp exists
    //def edit = {}

    //list.gsp exists
    def list = {
        [ sellingCompaniesAccountInstanceList: SellingCompaniesAccount.list( max:15) ]
        }

    //show.gsp exists
    //def show={}

    //save.gsp exists
    def save = { 
        log.info "Saving: " + params.toString()

        println("Saving: " + params.toString())
        def sellingCompaniesAccount = params.sellingCompaniesAccount
        println(sellingCompaniesAccount)

        def sellingCompanies = params.sellingCompanies

        log.info "sellingCompanies: " + sellingCompanies
        println(sellingCompanies)


        def sellingCompaniesAccountInstance = new SellingCompaniesAccount(name: params.name)

        println(params.name)

        params.each {
            if (it.key.contains("_sellingcompanies"))
            //sellingCompaniesAccountInstance.sellingCompaniesId << SellingCompanies.get((it.key - "sellingcompanies_") as Integer)
            if (it.key.contains("sellingcompanies_"))
                sellingCompaniesAccountInstance.sellingCompaniesId << SellingCompanies.get((it.key - "sellingcompanies_") as Integer)
        }
        log.info sellingCompaniesAccountInstance
        if (sellingCompaniesAccountInstance.save(flush: true)) {
            flash.message = "${message(code: 'default.created.message', args: [message(code: 'sellingCompaniesAccountInstance.label', default: 'sellingCompaniesAccountInstance'), sellingCompaniesAccountInstance.id])}"
            redirect(action: "show", id: sellingCompaniesAccountInstance.id)
            log.info sellingCompaniesAccountInstance
        }
        else {
            render(view: "create", model: [sellingCompaniesAccountInstance: sellingCompaniesAccountInstance])
        }


    }

}

Now, I am getting the following error, due to the empty hidden values appearing like _sellingcompanies_1 etc.:

Error Logs:

Saving: ["accountInfo.id":"1", "accountInfo":["id":"1"], "_sellingcompanies_5":"", "_isActive":"", "code":"test", "agency_name":"test", "sellingcompanies_4":"4", "sellingcompanies_5":"5", "create":"Create", "isActive":"on", "iata":"test", "agency_id":"test", "contactinfo.id":"1", "contactinfo":["id":"1"], "consultant_id":"test", "sellingcompanies_2":"2", "_sellingcompanies_1":"", "sellingcompanies_3":"3", "_sellingcompanies_2":"", "_sellingcompanies_3":"", "sellingcompanies_1":"1", "_sellingcompanies_4":"", "action":"save", "controller":"sellingCompaniesAccount"]
null
null
null
2011-03-15 17:13:44,620 [http-8080-2] ERROR org.codehaus.groovy.grails.web.errors.GrailsExceptionResolver - For input string: "_5"
java.lang.NumberFormatException: For input string: "_5"
    at java.lang.NumberFormatException.forInputString(NumberFormatException.java:48)
    at java.lang.Integer.parseInt(Integer.java:449)
    at java.lang.Integer.valueOf(Integer.java:554)
    at content_hub_admin.SellingCompaniesAccountController$_closure4_closure5.doCall(content_hub_admin.SellingCompaniesAccountController:70)
    at content_hub_admin.SellingCompaniesAccountController$_closure4.doCall(content_hub_admin.SellingCompaniesAccountController:66)
    at content_hub_admin.SellingCompaniesAccountController$_closure4.doCall(content_hub_admin.SellingCompaniesAccountController)
    at java.lang.Thread.run(Thread.java:680)

First of all, where does the hidden values come from and is this approach fine to commit the the Many-Many relationship info in SellingCompaniesAccount controller class. Any better technique of doing this.

The create.gsp resolves to this in the browser:

Thanks in advance

Evaginate

you should restrict the relevant code, it helps other reading far easier and understand your problem. – Juggernaut

as the log point out, it's a data-type error. Have you look at SellingCompaniesAccountController line 70? – Juggernaut

Thanks Hoang Long, after investigating, I found that the checkbox renders a hidden field.How can I disable this hidden field? Any ideas. And How can I add a record in SellingCompaniesAccount table for each checkbox selected. – Evaginate

Pretty sure the hidden field is there, so that you can "uncheck" the checkbox. Forms fail to submit checkbox values that are "unchecked", so if the checkbox WAS checked, but you've unchecked it, the hidden field must be there to let the property resolver know that the property USED to be checked. – Motorcade

Answer 1

If anyone else is having the same problem then the above answer by Daniel is absolutely correct just adding few changes as of Grails 2.7.8.

All checkbox will have same value = "${item.id}" and name ="sellingcompanies" as shown below:

<!-- ... snip ... -->
    <tr class="prop">
        <td valign="top" class="name"><label for="sellingCompanies"><g:message
            code="sellingCompaniesAccount.sellingCompanies.label"
            default="Selling Companies" /></label></td>
        <td valign="top"
            class="">
            <g:each in="${content_hub_admin.SellingCompanies.list()}" var="item" status="i">
                ${++i}.  ${item.name}&nbsp;&nbsp;<g:checkBox name="sellingcompanies" optionKey="id" value="${item.id}" /> <br>
            </g:each>
        <!--  end here by rsheyeah -->
        </td>
    </tr>
    <!-- ... snip ... -->

Best part is that in present version of Grails you wont need to use flatten() as string as mentioned by Daniel and these values will automatically be handled by Grails and will be persisted in the join table. All you need to be sure is your checkbox have correct name and value.

Hope it helps!

Francesco

Answer 2

The problem is with this piece of code:

    params.each {
        if (it.key.contains("_sellingcompanies"))
        //sellingCompaniesAccountInstance.sellingCompaniesId << SellingCompanies.get((it.key - "sellingcompanies_") as Integer)
        if (it.key.contains("sellingcompanies_"))
            sellingCompaniesAccountInstance.sellingCompaniesId << SellingCompanies.get((it.key - "sellingcompanies_") as Integer)
    }

Your params from the form post are:

Saving: ["accountInfo.id":"1", "accountInfo":["id":"1"], "_sellingcompanies_5":"", "_isActive":"", "code":"test", "agency_name":"test", "sellingcompanies_4":"4", "sellingcompanies_5":"5", "create":"Create", "isActive":"on", "iata":"test", "agency_id":"test", "contactinfo.id":"1", "contactinfo":["id":"1"], "consultant_id":"test", "sellingcompanies_2":"2", "_sellingcompanies_1":"", "sellingcompanies_3":"3", "_sellingcompanies_2":"", "_sellingcompanies_3":"", "sellingcompanies_1":"1", "_sellingcompanies_4":"", "action":"save", "controller":"sellingCompaniesAccount"]

The test in your loop first checks if the parameter key contains "_sellingcompanies" and then it does nothing; the second part of that checks if the parameter key contains "sellingcompanies_" and then it tries to pull the suffixed number off of that parameter value. In the case of a parameter with the key value "_sellingcompanies_5", both the first test and the second test evaluate to true, so in the second test, you are subtracting the "sellingcompanies_" off of the parameter key value and you are left with "_5", which you are then trying to evaluate to an Integer. Unfortunately, "_5" is not a valid Integer value, and hence your given error.

You can solve this very simply by doing the following:

params.each {
   if (it.key.startsWith("sellingcompanies")) {
      sellingCompaniesAccountInstance.sellingCompaniesId << SellingCompanies.get((it.key - "sellingcompanies_") as Integer)
   }
}

Probably a better way to handle this though would be to change your gsp to give the same named parameter for each of the sellingcompanies, and then looping through the actual applied values. Something like this:

        <!-- ... snip ... -->
        <tr class="prop">
            <td valign="top" class="name"><label for="sellingCompanies"><g:message
                code="sellingCompaniesAccount.sellingCompanies.label"
                default="Selling Companies" /></label></td>
            <td valign="top"
                class="">
                <g:each in="${content_hub_admin.SellingCompanies.list()}" var="item" status="i">
                    ${++i}.  ${item.name}&nbsp;&nbsp;<g:checkBox name="sellingcompanies" optionKey="id" value="${item.id}" /> <br>
                </g:each>
            <!--  end here by rsheyeah -->
            </td>
        </tr>
        <!-- ... snip ... -->

Then in your controller do something like this:

params.sellingcompanies = [params.sellingcompanies].flatten() as String[]

sellingCompaniesAccountInstance.sellingCompaniesId = params.sellingcompanies.collect { SellingCompanies.get(it) }

This should ensure that you are properly evaluating the appropriate values that have been passed from your model object, and not hacking in a retrieval method.

Hope this helps!

Sulphide