cascading jqgrids with initialCriteria specified

[ericraskin]

I am trying to output data using your Pet Clinic as an example. I will have three grids eventually, but for now I am just trying to cascade two grids. Note that both of my grids need to have initialCriteria (as I do not want to display all records in the database tables). Is this legal in the sub-grid?

My code contains:

    def brokerMailersGrid = {
        dataSourceType 'gorm'
        domainClass Customer
        initialCriteria {
            def user = springSecurityService.currentUser
            orders {
                mergeLists {
                    broker {
                        eq ('id', user.dbID)
                    }
                }
            }
            order("company")
        }
        enableFilter true
        inlineEdit false
        columns {
            id {
                type 'id'
                jqgrid {
                    hidden=true
                }
            }
            company {
                label "Mailer"
                filterClosure { Filter filter ->
                    ilike('company','%${filter.parnValue}%')
                }
                jqgrid {
                    width "250"
                }
            }
        }
    }

    def brokerMailerJobsGrid = {
        dataSourceType 'gorm'
        domainClass Order
        globalFilterClosure{ params ->
            eq('customer.id', params.customerId ? params.customerId as long: -1l)   
        }
        initialCriteria {
            def user = springSecurityService.currentUser
            mergeLists {
                broker {
                    eq ('id', user.dbID)
                }
            }
            order("bkrnum","desc")
        }
        enableFilter true
        inlineEdit false
        columns {
            ordnum {
                type 'id'
                label "PAS Num"
                enableFilter false
                jqgrid {
                    width "80"
                }
            }
            bkrnum {
                label "Job ID"
                filterClosure { Filter filter ->
                    ilike('bkrnum','%${filter.paramValue}%')
                }
                jqgrid {
                    width "150"
                }
            }
            orddate {
                label "Order Date"
                filterClosure { Filter filter ->
                    eq('orddate', new SimpleDateFormat('MM/dd/yyyy').parse(filter.paramValue))
                }
                jqgrid {
                    width "100"
                }
            }
        }
    }

Here are my view statements:

<grid:grid name="brokerMailers" jqgrid.width="450" jqgrid.caption='"Mailers"'/>
<grid:grid name="brokerMailerJobs" masterGrid="brokerMailers" childParamName="customerId" jqgrid.width="450" jqgrid.caption='"Merges"'/>
    <r:script>
        function addElement(lnk, gridId, param, master){
            var elem = jQuery('#'+gridId+'_table').jqGrid('getGridParam', 'selrow');
            console.log(elem);
            if(jQuery.isEmptyObject(elem)){
                alert("You have to select a "+master+" first");
                return;
            }
            document.location = lnk+"?"+param+"="+elem;;    
        }
    </r:script>
    <jq:jquery>
        console.log(jQuery('#brokerMailers_table').jqGridMethod('setSelection', 1));
    </jq:jquery>

When I execute this, I get a 404 error on the screen:

and this in the log (truncated the stack trace):

| Error 2014-03-19 12:07:51,768 [http-bio-8443-exec-1] ERROR errors.GrailsExceptionResolver - MissingMethodException occurred when processing request: [GET] /pasweb/customerAccess/brokerMailerJobsRows - parameters:
sord: asc
page: 1
nd: 1395245271519
sidx:
format:
rows: 20
_search: false
No signature of method: pasweb.CustomerAccessController$_closure2_closure13.doCall() is applicable for argument types: (org.codehaus.groovy.grails.web.servlet.mvc.GrailsParameterMap, grails.gorm.DetachedCriteria) values: [[sord:asc, page:1, nd:1395245271519, sidx:, ...], ...]
Possible solutions: doCall(java.lang.Object), call(), call([Ljava.lang.Object;), call(java.lang.Object), findAll(). Stacktrace follows:
Message: No signature of method: pasweb.CustomerAccessController$_closure2_closure13.doCall() is applicable for argument types: (org.codehaus.groovy.grails.web.servlet.mvc.GrailsParameterMap, grails.gorm.DetachedCriteria) values: [[sord:asc, page:1, nd:1395245271519, sidx:, ...], ...]
Possible solutions: doCall(java.lang.Object), call(), call([Ljava.lang.Object;), call(java.lang.Object), findAll()
Line | Method
->> 55 | unwrap in org.codehaus.groovy.runtime.ScriptBytecodeAdapter

---

| -1 | call in groovy.lang.Closure$call$2
| 45 | defaultCall . . . . . . . . . . . . . in org.codehaus.groovy.runtime.callsite.CallSiteArray
| 108 | call in org.codehaus.groovy.runtime.callsite.AbstractCallSite
| 116 | call . . . . . . . . . . . . . . . . in ''
| 210 | doCall in org.grails.plugin.easygrid.datasource.GormDatasourceService$_createWhereQuery_closure8
| -2 | invoke0 . . . . . . . . . . . . . . . in sun.reflect.NativeMethodAccessorImpl
| 57 | invoke in ''
| 43 | invoke . . . . . . . . . . . . . . . in sun.reflect.DelegatingMethodAccessorImpl
| 606 | invoke in java.lang.reflect.Method
| 1254 | jlrMethodInvoke . . . . . . . . . . . in org.springsource.loaded.ri.ReflectiveInterceptor
| 90 | invoke in org.codehaus.groovy.reflection.CachedMethod
| 233 | doMethodInvoke . . . . . . . . . . . in groovy.lang.MetaMethod
| 1086 | invokeMethod in groovy.lang.MetaClassImpl
| 1110 | invokeMethod . . . . . . . . . . . . in groovy.lang.ExpandoMetaClass
| 910 | invokeMethod in groovy.lang.MetaClassImpl
| 411 | call . . . . . . . . . . . . . . . . in groovy.lang.Closure

[tudor-malene]

On a first glance the problem is here:

globalFilterClosure{ params ->
        eq('customer.id', params.customerId ? params.customerId as long: -1l)   
 }

should be:

globalFilterClosure{ params ->
      customer{
        eq('id', params.customerId ? params.customerId as long: -1l)   
      } 
}

[ericraskin]

Thanks. Could you determine this from the error messages somehow or is it just something you know.

How would a newbie like me figure this out on my own?

[ericraskin]

Unfortunately, that was not the only issue. I still get the following (again, stacktrace truncated):

| Error 2014-03-19 14:05:56,340 [http-bio-8443-exec-1] ERROR errors.GrailsExceptionResolver - MissingMethodException occurred when processing request: [GET] /pasweb/customerAccess/brokerMailerJobsRows - parameters:
sord: asc
page: 1
nd: 1395252356081
sidx:
format:
rows: 20
_search: false
No signature of method: pasweb.CustomerAccessController$_closure2_closure13.doCall() is applicable for argument types: (org.codehaus.groovy.grails.web.servlet.mvc.GrailsParameterMap, grails.gorm.DetachedCriteria) values: [[sord:asc, page:1, nd:1395252356081, sidx:, ...], ...]
Possible solutions: doCall(java.lang.Object), call(), call([Ljava.lang.Object;), call(java.lang.Object), findAll(). Stacktrace follows:
Message: No signature of method: pasweb.CustomerAccessController$_closure2_closure13.doCall() is applicable for argument types: (org.codehaus.groovy.grails.web.servlet.mvc.GrailsParameterMap, grails.gorm.DetachedCriteria) values: [[sord:asc, page:1, nd:1395252356081, sidx:, ...], ...]
Possible solutions: doCall(java.lang.Object), call(), call([Ljava.lang.Object;), call(java.lang.Object), findAll()
Line | Method
->> 55 | unwrap in org.codehaus.groovy.runtime.ScriptBytecodeAdapter

---

| -1 | call in groovy.lang.Closure$call$2
| 45 | defaultCall . . . . . . . . . . . . . in org.codehaus.groovy.runtime.callsite.CallSiteArray
| 108 | call in org.codehaus.groovy.runtime.callsite.AbstractCallSite
| 116 | call . . . . . . . . . . . . . . . . in ''
| 210 | doCall in org.grails.plugin.easygrid.datasource.GormDatasourceService$_createWhereQuery_closure8
| -2 | invoke0 . . . . . . . . . . . . . . . in sun.reflect.NativeMethodAccessorImpl
| 57 | invoke in ''
| 43 | invoke . . . . . . . . . . . . . . . in sun.reflect.DelegatingMethodAccessorImpl
| 606 | invoke in java.lang.reflect.Method
| 1254 | jlrMethodInvoke . . . . . . . . . . . in org.springsource.loaded.ri.ReflectiveInterceptor
| 90 | invoke in org.codehaus.groovy.reflection.CachedMethod
| 233 | doMethodInvoke . . . . . . . . . . . in groovy.lang.MetaMethod
| 1086 | invokeMethod in groovy.lang.MetaClassImpl
| 1110 | invokeMethod . . . . . . . . . . . . in groovy.lang.ExpandoMetaClass
| 910 | invokeMethod in groovy.lang.MetaClassImpl
| 411 | call . . . . . . . . . . . . . . . . in groovy.lang.Closure
| -1 | call in org.grails.plugin.easygrid.datasource.GormDatasourceService$_createWhereQuery_closure8
| 427 | call . . . . . . . . . . . . . . . . in groovy.lang.Closure
| -1 | call in org.grails.plugin.easygrid.datasource.GormDatasourceService$_createWhereQuery_closure8
| 1325 | each . . . . . . . . . . . . . . . . in org.codehaus.groovy.runtime.DefaultGroovyMethods
| 1297 | each in ''
| -1 | invoke . . . . . . . . . . . . . . . in org.codehaus.groovy.runtime.dgm$148
| 271 | invoke in org.codehaus.groovy.runtime.callsite.PojoMetaMethodSite$PojoMetaMethodSiteNoUnwrapNoCoerce
| 53 | call . . . . . . . . . . . . . . . . in org.codehaus.groovy.runtime.callsite.PojoMetaMethodSite
| 45 | defaultCall in org.codehaus.groovy.runtime.callsite.CallSiteArray
| 108 | call . . . . . . . . . . . . . . . . in org.codehaus.groovy.runtime.callsite.AbstractCallSite
| 116 | call in ''
| 207 | createWhereQuery . . . . . . . . . . in org.grails.plugin.easygrid.datasource.GormDatasourceService
| -1 | callCurrent in org.grails.plugin.easygrid.datasource.GormDatasourceService$createWhereQuery$3
| 49 | defaultCallCurrent . . . . . . . . . in org.codehaus.groovy.runtime.callsite.CallSiteArray
| 133 | callCurrent in org.codehaus.groovy.runtime.callsite.AbstractCallSite
| 145 | callCurrent . . . . . . . . . . . . . in ''
| 173 | list in org.grails.plugin.easygrid.datasource.GormDatasourceService
| -1 | invoke . . . . . . . . . . . . . . . in org.grails.plugin.easygrid.datasource.GormDatasourceService$$FastClassBySpringCGLIB$$3b1ff7f9
| 204 | invoke in org.springframework.cglib.proxy.MethodProxy
| 700 | invokeJoinpoint . . . . . . . . . . . in org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation
| 150 | proceed in org.springframework.aop.framework.ReflectiveMethodInvocation
| 96 | proceedWithInvocation . . . . . . . . in org.springframework.transaction.interceptor.TransactionInterceptor$1
| 260 | invokeWithinTransaction in org.springframework.transaction.interceptor.TransactionAspectSupport
| 94 | invoke . . . . . . . . . . . . . . . in org.springframework.transaction.interceptor.TransactionInterceptor
| 172 | proceed in org.springframework.aop.framework.ReflectiveMethodInvocation
| 633 | intercept . . . . . . . . . . . . . . in org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor
| -1 | list in org.grails.plugin.easygrid.datasource.GormDatasourceService$$EnhancerBySpringCGLIB$$81e13b1d
| 45 | defaultCall . . . . . . . . . . . . . in org.codehaus.groovy.runtime.callsite.CallSiteArray
| 108 | call in org.codehaus.groovy.runtime.callsite.AbstractCallSite
| 124 | call . . . . . . . . . . . . . . . . in ''
| 77 | callDSList in org.grails.plugin.easygrid.EasygridDispatchService
| -1 | call . . . . . . . . . . . . . . . . in org.grails.plugin.easygrid.EasygridDispatchService$callDSList$9
| 45 | defaultCall in org.codehaus.groovy.runtime.callsite.CallSiteArray
| 108 | call . . . . . . . . . . . . . . . . in org.codehaus.groovy.runtime.callsite.AbstractCallSite
| 124 | call in ''
| 82 | gridData . . . . . . . . . . . . . . in org.grails.plugin.easygrid.EasygridService
| -1 | call in org.grails.plugin.easygrid.EasygridService$gridData$4
| 72 | doCall . . . . . . . . . . . . . . . in org.grails.plugin.easygrid.EasygridInitService$_registerControllerMethods_closure4
| -1 | doCall in ''
| -2 | invoke0 . . . . . . . . . . . . . . . in sun.reflect.NativeMethodAccessorImpl
| 57 | invoke in ''
| 43 | invoke . . . . . . . . . . . . . . . in sun.reflect.DelegatingMethodAccessorImpl
| 606 | invoke in java.lang.reflect.Method
| 1254 | jlrMethodInvoke . . . . . . . . . . . in org.springsource.loaded.ri.ReflectiveInterceptor
| 272 | invoke in org.codehaus.groovy.runtime.callsite.PogoMetaMethodSite$PogoCachedMethodSiteNoUnwrapNoCoerce
| 64 | call . . . . . . . . . . . . . . . . in org.codehaus.groovy.runtime.callsite.PogoMetaMethodSite
| 112 | call in org.codehaus.groovy.runtime.callsite.AbstractCallSite
| 207 | guard . . . . . . . . . . . . . . . . in org.grails.plugin.easygrid.EasygridService
| -1 | call in org.grails.plugin.easygrid.EasygridService$guard$3
| 111 | doCall . . . . . . . . . . . . . . . in org.grails.plugin.easygrid.EasygridInitService$_registerControllerMethods_closure9_closure17
| -1 | doCall in ''
| -2 | invoke0 . . . . . . . . . . . . . . . in sun.reflect.NativeMethodAccessorImpl
| 57 | invoke in ''
| 43 | invoke . . . . . . . . . . . . . . . in sun.reflect.DelegatingMethodAccessorImpl
| 606 | invoke in java.lang.reflect.Method
| 1254 | jlrMethodInvoke . . . . . . . . . . . in org.springsource.loaded.ri.ReflectiveInterceptor
| 90 | invoke in org.codehaus.groovy.reflection.CachedMethod
| 233 | doMethodInvoke . . . . . . . . . . . in groovy.lang.MetaMethod
| 1086 | invokeMethod in groovy.lang.MetaClassImpl
| 1110 | invokeMethod . . . . . . . . . . . . in groovy.lang.ExpandoMetaClass
| 910 | invokeMethod in groovy.lang.MetaClassImpl
| 411 | call . . . . . . . . . . . . . . . . in groovy.lang.Closure
| -1 | call in org.grails.plugin.easygrid.EasygridInitService$_registerControllerMethods_closure9_closure17
| 405 | call . . . . . . . . . . . . . . . . in groovy.lang.Closure
| -1 | call in org.grails.plugin.easygrid.EasygridInitService$_registerControllerMethods_closure9_closure17
| 156 | invoke . . . . . . . . . . . . . . . in org.codehaus.groovy.grails.web.servlet.mvc.MixedGrailsControllerHelper
| 354 | handleAction in org.codehaus.groovy.grails.web.servlet.mvc.AbstractGrailsControllerHelper
| 231 | executeAction . . . . . . . . . . . . in ''
| 197 | handleURI in ''
| 121 | handleURI . . . . . . . . . . . . . . in ''
| 72 | handleRequest in org.codehaus.groovy.grails.web.servlet.mvc.SimpleGrailsController
| 48 | handle . . . . . . . . . . . . . . . in org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter
| 355 | doDispatch in org.codehaus.groovy.grails.web.servlet.GrailsDispatcherServlet
| 856 | doService . . . . . . . . . . . . . . in org.springframework.web.servlet.DispatcherServlet
| 953 | processRequest in org.springframework.web.servlet.FrameworkServlet
| 844 | doGet . . . . . . . . . . . . . . . . in ''
| 620 | service in javax.servlet.http.HttpServlet

[ericraskin]

So, a little more on this. Don't know if it is helpful. I stepped through your code and here is where it fails:

    Criteria createWhereQuery(GridConfig gridConfig, List<Filter> filters) {
        DetachedCriteria baseCriteria = new DetachedCriteria(gridConfig.domainClass)
        if (gridConfig.initialCriteria) {
            baseCriteria = baseCriteria.build(gridConfig.initialCriteria)
        }

        filters.collect { getCriteria(it) }.each { Closure filterCriteria ->
            filterCriteria.resolveStrategy = Closure.DELEGATE_FIRST
            filterCriteria.delegate = baseCriteria
            filterCriteria(baseCriteria)
        }

        // add the filterpane stuff -if supported
/*
        if (filterPaneService) {
            filterPaneService.addFiltersToCriteria(result, params, gridConfig.domainClass)
        }
*/
        return baseCriteria
    }

It dies on the filterCriteria(baseCriteria) call inside the filters.collect{ } statement. It is trying to combine the globalFilterClosure with the initialCriteria, as far as I can tell. For the fun of it, I removed the globalFilterClosure and the error went away. Of course, the tables weren't correct, but the error was gone.

Maybe this will help determine what I am doing wrong?

(PS. Sorry for posting so many times in a row...)

[ericraskin]

Just in case it helps, here are the definitions of the domains in use. The definitions are:

Customer - a customer (obviously)
Order - order placed by a customer
Mplist - on one kind of order (a merge job), there are a bunch of lists processed. One record per list in the job
Broker - each list has been ordered by a third party, called a broker

This interface is for the Broker to see the lists they've ordered by customer and job. They are responsible to make sure that the lists they've ordered arrive here on time. They log in, and we have stored their internal ID in the User.dbID field. We query the Mplists ordered by the brokers, then back to the Orders that contain those Mplists, then back to the Customers that placed those Orders.

I am planning on displaying cascading tables. The first is the Customers that have Orders containing Mplists ordered by the Broker who has logged in. When they click on a Customer, the Orders that have Mplists ordered by the Broker get displayed. Finally, when they click on an Order, the Mplists will themselves display.

Hopefully this makes some sense.

class Customer implements Comparable<Customer> {

    Long        id
    String      company
    String      division
    String      address_1
    String      address_2
    String      city
    String      state
    String      zip
    String      zip4
    String      country
    String      website
    String      mpemail
    String      contact
    String      phone
    String      fax
    String      email
    SortedSet   orders
    SortedSet   ownedRentalLists
    SortedSet   maanagedRentalLists

    static  hasMany = [ orders: Order, ownedRentalLists: RentalList, managedRentalLists: RentalList ]
    static mappedBy = [ orders: 'customer', ownedRentalLists: 'owner', managedRentalLists: 'manager' ]

    static mapping = {
        table name: 'customers', schema:'sbowner'
        version false
        id column: 'id', generator: 'assigned'
        state(sqlType:'char')
        zip4(sqlType:'char')
    }

    static constraints = {
    }

    int compareTo(Customer obj) {
        company <=> obj.company
    }
}

class Order implements Comparable<Order> {

    Long        ordnum
    String      ord_type
    Date        rundate
    Date        orddate
    Date        wantdate
    Date        maildate
    Date        shipdate
    Date        qtyappdate
    String      cancelled
    String      status
    String      mgrnum
    String      bkrnum
    Long        seedcount
    Long        seedrecd
    Date        seeddate
    Customer    customer
    RentalList  rentalList
    Mailer      mailer
    SortedSet   mergeLists

    static hasMany = [ mergeLists: Mplist ]
    static mappedBy = [ mergeLists: 'order' ]

    static mapping = {
        table name: 'orders', schema:'sbowner'
        version false
        id column: 'ordnum', name: 'ordnum', generator: 'assigned'
        customer column: 'cus_id'
        rentalList column: 'lst_id'
        mailer column: 'mlr_id'
        cancelled(sqlType:'char')
        status(sqlType:'char')
    }

    static constraints = {
    }

    @Override String toString() {
        return "${ordnum}:${ord_type}"
    }

    int compareTo(Order obj) {
        ordnum <=> obj.ordnum
    }

}

class Mplist implements Comparable<Mplist> {

    Long    id
    String  keycode
    String  mpl_type
    Long    priority
    String  list
    String  segment
    String  brokerpo
    Long    ordqty
    Long    actqty
    Date    daterecd
    Date    datechecked
    Date    dateused
    String  status
    String  qtyflag
    Order   order
    Broker  broker

    static mapping = {
        table name: 'mplists', schema:'sbowner' 
        version false
        id column: 'id', generator: 'assigned'
        broker column: 'brk_id'
        order column: 'ord_ordnum', insertable:false, updatable: false
        mpl_type(sqlType:'char')
        status(sqlType:'char')
        qtyflag(sqlType:'char')
    }

    static constraints = {
    }

    @Override String toString() {
        return "${order.ordnum}:${keycode}:${list}"
    }

    int compareTo(Mplist obj) {
        order.ordnum <=> obj.order.ordnum ?: keycode <=> obj.keycode
    }
}

class Broker {

    Long        id
    String      company
    String      division
    String      address_1
    String      address_2
    String      city
    String      state
    String      zip
    String      zip4
    String      country
    String      website
    String      mpemail
    String      contact
    String      phone
    String      fax
    String      email
    SortedSet   brokeredLists

    static hasMany = [ brokeredLists: Mplist ]
    static mappedBy = [ brokeredLists: 'broker' ]

    static mapping = {
        table name: 'brokers', schema: 'sbowner'
        version false
        id column: 'id', generator: 'assigned'
        state(sqlType:'char')
        zip4(sqlType:'char')
    }

    @Override String toString() {
        return "${id}:${company}"
    }

    static constraints = {
    }
}

[ericraskin]

Not to belabor the point, but I put the following into a Service and it worked perfectly:

        def crit = Order.createCriteria().listDistinct {
            def user = springSecurityService.currentUser
            customer {
                // known customer ID as a test default
                eq('id', params.customerId ? params.customerId as long: 18350499l)   
            }
            mergeLists {
                broker {
                    eq ('id', user.dbID)
                }
            }
            order("bkrnum","desc")
        }

So, it appears something is wrong in the Easygrid code. Please let me know what else I can do to help you find the problem.

[tudor-malene]

This is indeed a bug in easygrid, which I fixed in the development version already. ( actually it's more of a weird behavior of gorm )

In order to fix it for you locally, until the next release, you can do the following.
Go to GormDatasourceService at line 210 and :

    filters.collect { getCriteria(it) }.each { Closure filterCriteria ->
        filterCriteria.resolveStrategy = Closure.DELEGATE_FIRST
        filterCriteria.delegate = baseCriteria
        filterCriteria()
    }

( basically just remove baseCriteria )

[ericraskin]

Thanks! I will try it. Looking forward to that next release. :-)
On Mar 20, 2014 4:41 AM, "Tudor Malene" notifications@github.com wrote:

This is indeed a bug in easygrid, which I fixed in the development version
already. ( actually it's more of a weird behavior of gorm )

In order to fix it for you locally, until the next release, you can do the
following.
Go to GormDatasourceService at line 210 and :

filters.collect { getCriteria(it) }.each { Closure filterCriteria ->
    filterCriteria.resolveStrategy = Closure.DELEGATE_FIRST
    filterCriteria.delegate = baseCriteria
    filterCriteria()
}

( basically just remove baseCriteria )

Reply to this email directly or view it on GitHubhttps://github.com//issues/54#issuecomment-38144909
.

[ericraskin]

Yes -- that fixed it. Now just looking forward to the DISTINCT option and I'm good to go!

Thanks very much for your help (and for the plugin).

[tudor-malene]

You can fix the distinct issue locally by changing the last line of the 'list' method in GormDatasourceService to:

    def listMethod = 'list'
    if (gridConfig.listDistinct) {
        listMethod = 'listDistinct'
    }

    addOrderBy(createWhereQuery(gridConfig, filters), orderBy)."${listMethod}"(max: listParams.maxRows, offset: listParams.rowOffset)

and by adding :

 def brokerMailersGrid = {
     dataSourceType 'gorm'
     domainClass Customer
     listDistinct true
  ...

to your grid

[ericraskin]

Suprisingly, it did not work:

No signature of method: grails.gorm.DetachedCriteria.listDistinct() is
applicable for argument types: (java.util.LinkedHashMap) values:
[[max:20, offset:0]]. Stacktrace follows:
Message: No signature of method:
grails.gorm.DetachedCriteria.listDistinct() is applicable for argument
types: (java.util.LinkedHashMap) values: [[max:20, offset:0]]

Here is the code I entered (cut and pasted from the
GormDataSource.groovy file):

        def listMethod = 'list'
        if (gridConfig.listDistinct) {
            listMethod = 'listDistinct'
        }
        addOrderBy(createWhereQuery(gridConfig, filters),
orderBy)."${listMethod}"(max: listParams.maxRows, offset:
listParams.rowOffset)

Funny thing -- the documentation doesn't show a listDistinct method for
DetachedCriteria.

Method Description
list List all matching entities
get Return a single matching result
count Count all matching records
exists Return true if any matching records exist
deleteAll Delete all matching records
updateAll(Map) Update all matching records with the given properties

There is a listDistinct on createCriteria.

I also tried adding a ".unique" to the end of your statement when
listDistinct is true, but that just hung -- might be very slow.

I haven't come up with a good solution yet. I've tried projections
{distinct ... }} and setResultTransformer(Criteria.DISTINT_ROOT_ENTITY)
inside the criteria but they don't work -- at least not on
DetachedCriteria.

Any suggestions? Are we getting to the point where I have to create a
custom Service using HQL to load this table?

On 03/20/2014 10:43 AM, Tudor Malene wrote:

You can fix the distinct issue locally by changing the last line of
the 'list' method in GormDatasourceService to:

| def listMethod = 'list'
if (gridConfig.listDistinct) {
listMethod = 'listDistinct'
}

addOrderBy(createWhereQuery(gridConfig, filters), orderBy)."${listMethod}"(max: listParams.maxRows, offset: listParams.rowOffset)

|
|

and by adding :

| def brokerMailersGrid = {
dataSourceType 'gorm'
domainClass Customer
listDistinct true
...
|

to your grid

—
Reply to this email directly or view it on GitHub
#54 (comment).

---

Eric H. Raskin 914-765-0500 x120
Professional Advertising Systems Inc. 914-765-0503 fax
200 Business Park Dr Suite 304 eraskin@paslists.com
Armonk, NY 10504 http://www.paslists.com

[tudor-malene]

Yes , you're right.
Not working.

Is your dataset very big?
If not, we can implement a hook closure that could further transform the list returned by the criteria.list method

[ericraskin]

I think the jqGrid is saying about 24,000 rows. Are you suggesting that we
remove the duplicates on the fly in the client? Won't that be slow?

Yes , you're right.
Not working.

Is your dataset very big?
If not, we can implement a hook closure that could further transform the
list returned by the criteria.list method

Reply to this email directly or view it on
GitHubhttps://github.com//issues/54#issuecomment-38193800
.

[ericraskin]

So I did a little more digging around. There is a "distinct" projection
for a criteria, but it does not work properly when you use pagination.
Here's what happens:

        initialCriteria {
            def user = springSecurityService.currentUser
            orders {
                mergeLists {
                    broker {
                        eq ('id', user.dbID)
                    }
                }
            }
            projections {
                distinct("company")
            }
            order("company")
        }

generates the following SQL:

select distinct this_.company as y0_,
       this_.id as y1_,
       count(*) as y2_
from sbowner.customers this_
     inner join sbowner.orders orders_ali1_ on this_.id=orders_ali1_.cus_id
     inner join sbowner.mplists mergelists2_ on
orders_ali1_.ordnum=mergelists2_.ord_ordnum
     inner join sbowner.brokers broker_ali3_ on
mergelists2_.brk_id=broker_ali3_.id
where broker_ali3_.id=?
order by this_.company asc

This is actually ALMOST correct. You can't do the count() at the same
time as the distinct, without a group by. If you add the group by, then
the count() contains the wrong count (count of entries for each
customer) rather than the count of the total number of distinct
customers needed for pagination.

I have NO idea how to get around this fundamental flaw, unless you can
figure out a way to give me HQL access? If I can write HQL rather than
an initialCriteria, then I can generate exactly what I want. The HQL
has to be able to accept parameters, of course.

        result.customers = Order.executeQuery('select distinct c.company
company, c.id id from Customer c join c.orders o join o.mergeLists m
join m.broker b where b.id = ? order by 1', [ user.dbID ], params )
        result.count = Order.executeQuery('select count(distinct
c.company) from Customer c join c.orders o join o.mergeLists m join
m.broker b where b.id = ? order by 1', [ user.dbID ], params )

What do you think?

[ericraskin]

Amazingly, after hours of web searching, I found a solution that works! Here is my initialCriteria:

       initialCriteria {
            def sub = new DetachedCriteria(Customer).build {
                def user = springSecurityService.currentUser
                orders {
                    mergeLists {
                        broker {
                            eq ('id', user.dbID)
                        }
                    }
                }
                projections {
                    distinct("id")
                }
            }
            'in' ('id', sub.list() ?: [OL])
            order("company")
        }

Basically, I create a sub-query that returns distinct customer IDs. Then the outer query does an "in" clause against them, pulling all customers with IDs in the sub-query.

This actually works! Of course, it is a bit inefficient, requiring two queries to get the results, but it is certainly better than nothing.

[tudor-malene]

Thanks for digging on this.

Here are my thoughts:

1. The solution with the projection without the subquery:
   The main drawback, as I see it, is that you can't count properly and that probably the pagination stuff won't return the same numbers of rows each time
   But overall, the user experience might not suffer all that much.

2. The solution with the subquery :
   Works perfectly functionally. But can be quite inefficient on a large dataset.

3. Adding Hql support to easygrid:
   This could be a good idea for edge cases, but it would make everything very very ugly

I, personally incline more towards 1 ( if it is even possible) and 2 - if the performance is reasonable.

[ericraskin]

Agreed. Option 2 works best for us. We don't have a very large
database of customers (unfortunately). So, the dual query solution
populates very quickly.

On 03/21/2014 04:59 AM, Tudor Malene wrote:

Thanks for digging on this.

Here are my thoughts:

1. The solution with the projection without the subquery:
   The main drawback, as I see it, is that you can't count properly and
   that probably the pagination stuff won't return the same numbers of
   rows each time
   But overall, the user experience might not suffer all that much.

2. The solution with the subquery :
   Works perfectly functionally. But can be quite inefficient on a large
   dataset.

3. Adding Hql support to easygrid:
   This could be a good idea for edge cases, but it would make everything
   very very ugly

I, personally incline more towards 1 ( if it is even possible) and 2 -
if the performance is reasonable.

—
Reply to this email directly or view it on GitHub
#54 (comment).

---

Eric H. Raskin 914-765-0500 x120
Professional Advertising Systems Inc. 914-765-0503 fax
200 Business Park Dr Suite 304 eraskin@paslists.com
Armonk, NY 10504 http://www.paslists.com

[tudor-malene]

This issue proved to be trickier than expected.

I'll try to play with option 1 when I will get the chance.

In you case, to improve performance, you can cache the distinct customer ids (and possible store the cache in the user session), or de-normalize the db a little,

[tudor-malene]

Hi Eric, please try easygrid 1.5.0 - when you get the chance.

See this example:
http://199.231.186.169:8080/petclinic/vetSchedule/overview

Thanks