Populate dropdown list using ajax In grails

Question

we are trying to implement ajax in new incident to get values of assign user id by click on groups for that we apply ajax in views on create.gsp

ajax used in create.gsp

<g:javascript>document.observe('dom:loaded', function() {$("groupId").observe("change", respondToSelect);});        
            function respondToSelect(event)
            {new Ajax.Updater("userId","/smartserv/incident/updateSelect",{method:'get', parameters: {selectedValue : $F("groupId")} });}</g:javascript>

this are tags used in create.gsp

<tr class="prop"> <td valign="top" class="name">
                                    <label for="groupId"><g:message code="incident.groupId.label" default="Assignment Group" /></label>
                                </td>
                                <td valign="top" class="value ${hasErrors(bean: incidentInstance, field: 'groupId', 'errors')}">
                                    <g:select id="groupId" name="groupId" from="${app.Usergroup.list()}" optionKey="id" value="${incidentInstance?.groupId?.id}"  />
                                </td>
                            </tr>
                            <div id="userId">
                            <tr class="prop">
                                <td valign="top" class="name">
                                    <label for="userId"><g:message code="incident.userId.label" default="Assign Id" /></label>
                                </td>
                                <td valign="top" class="value ${hasErrors(bean: incidentInstance, field: 'userId', 'errors')}">
                                    <g:select  name="userId" from="${app.User.list()}" optionKey="id" value="${incidentInstance?.userId?.id}"  />
                                </td>
                            </tr> 
                            </div> 

In incident controller we used updateSelect to get values

def dataSource
   def updateSelect = {
             def sql = new Sql(dataSource)
                         def user_id = sql.execute("SELECT distinct user_id FROM user_groups where user_groups.usergroup_id='+usergroup_id+'")
  render(usergroup_id:params.selectedValue,template:"assignId", model : ['user_id' : user_id])
}        

_assignId.gsp

<g:select
       from="${groupId.members}"
       name="" value="">
</g:select>

database table:group_(fields:groupid , description, application)

table:user(fields:userid,username,etc)

table:user_groups( fields:userid and usergroupid)

by using this tables we have to get values by using ajax

we are getting action on assignid but we are not getting particular values And Can anyone suggest website or pdf that how to implement sql queries in controller.

Answer 1

i am work on that i got answer for above question i am posting my answer.

In controller i wrote this code

import grails.converters.*
def ajaxGetMembers = {
        def group = Usergroup.get(params.id)
        render group?.members as JSON
    } 

In create.gsp

<g:javascript library="prototype"/>
         <g:javascript>
    function updateUser(e) {
        // The response comes back as a bunch-o-JSON
        var members = eval("(" + e.responseText + ")")  // evaluate JSON

        if (members) {
            var rselect = document.getElementById('user')

            // Clear all previous options
            var l = rselect.length

            while (l > 0) {
                l--
                rselect.remove(l)
            }

            // Rebuild the select
            for (var i=0; i < members.length; i++) {
                var user = members[i]
                var opt = document.createElement('option');
                opt.text = user.userId
                opt.value = user.id
                try {
                    rselect.add(opt, null) // standards compliant; doesn't work in IE
                }
                catch(ex) {
                    rselect.add(opt) // IE only
                }
            }
        }
    }


    // This is called when the page loads to initialize city
    var zselect = document.getElementById('user.id')
    var zopt = zselect.options[zselect.selectedIndex]
    ${remoteFunction(controller:"incident", action:"ajaxGetMembers", params:"'id=' + zopt.value", onComplete:"updateUser(e)")}

</g:javascript> 

place above code in header part in create.gsp

<tr class="prop">
                                <td valign="top" class="name">
                                    <label for="group"><g:message code="incident.group.label" default="Group" /></label>
                                </td>
                                <td valign="top" class="value ${hasErrors(bean: incidentInstance, field: 'group', 'errors')}">
                                    <g:select  optionKey="id"  name="group.id" id="group.id" from="${app.Usergroup.list()}"
        onchange="${remoteFunction(
            controller:'incident', 
            action:'ajaxGetMembers', 
           params:'\'id=\' + this.value', 
            onComplete:'updateUser(e)')}"
    ></g:select>
                                </td>
                            </tr>


                            <tr class="prop">
                                <td valign="top" class="name">
                                    <label for="user"><g:message code="incident.user.label" default="User" /></label>
                                </td>
                                <td valign="top" class="value ${hasErrors(bean: incidentInstance, field: 'user', 'errors')}">
                                   <g:select name="user.id" optionKey="id" id="user" selectedValue="groupId"  ></g:select>


                                </td>
                            </tr>

Answer 2

You do not need to write javascript for this. Use the remoteFunction tag/function to do this:

<g:select onchange="${remoteFunction(
                    action:'updateSelect', 
                    params:'\'groupId=\' + this.value', 
                    update:'userId',
                    id:'groupId',
                    name:'groupId',
                    from:'${app.Usergroup.list()}', 
                    optionKey:'id', 
                    value:'${incidentInstance?.groupId?.id}')}" />

On the server side do:

def updateSelect = {
    def userList = UserGroup.get(params.groupId).user;

    render(template:"assignId", model: ['userList' : userList])
}        

good luck ;)