DetachedCriteria vs findAllBy... two different results same constraints!?

[geezenslaw]

Hello Grails all, pls forget the noise in the previous post on this issue.

I'm getting  two different resultsets depending upon whether I use
DetachedCriter or findAllBy...
where the findAllBy is fairly long but appears to correct except for the
one reason:

Neither query returns the same result set for the same constraints.

The oddity is the DBA knows that both result sets should be returned in
a single query to fairly represent the constraints used in both queries.

Any ideas what is causing this?

BTW: if createCriteria is used the resultset for the DetachedCriteria is
duplicated upon return.

This returns one resultset
********************************************************************************
def results =
VviewGlDetailAccount.findAllByCountyNbrAndDistrictNbrAndTransactionIdLikeAndFundCodeLikeAndResourceCodeLikeAndGlAcctCodeNotEqual(
                 countyNbr,
                 districtNbr,
                 transID,
                 fundCode,
                 resCode,
                 '0000')
********************************************************************************



This returns a similar result set except for the glAcctCode
********************************************************************************
def criteria = new DetachedCriteria(VviewGlDetailAccount).build {
             eq('countyNbr', countyNbr)
             eq('districtNbr', districtNbr)
             like('transactionId', transID)
             ge('transactionDate',beginDate)
             le('transactionDate',endDate)
             like('fundCode', fundCode)
             like('glAcctCode', glAcctCode)
             ne('glAcctCode', '0000')
             like('resourceCode', resCode)
             order('transactionId', 'asc')
             order('transactionDate', 'asc')
             order('fundCode', 'asc')
             order('resourceCode', 'asc')
             order('glAcctCode', 'asc')
             firstResult(offset)
             cache(true)
             maxResults(max)
         }

         def results = criteria.list()

Please advise, David.

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email

[Gary Affonso]

I know that pagination related params cause the querying system to return different resultset types.  I believe it also affects the number of queries run (if pagination params are present, I think an additional count-related query is run).

I see that your first findbyAll query does not include pagination params.

It looks like your second query is including pagination-related commands (max, etc.) so my guess as to the different resultset types would be that feature of grails.

Don't know why you'd be getting different contents in those resultsets, though.  But if I were digging into this, I'd start there.

- g

On Jun 25, 2012, at 2:07 PM, David Brown wrote:

> Hello Grails all, pls forget the noise in the previous post on this issue.
>
> I'm getting  two different resultsets depending upon whether I use DetachedCriter or findAllBy...
> where the findAllBy is fairly long but appears to correct except for the one reason:
>
> Neither query returns the same result set for the same constraints.
>
> The oddity is the DBA knows that both result sets should be returned in a single query to fairly represent the constraints used in both queries.
>
> Any ideas what is causing this?
>
> BTW: if createCriteria is used the resultset for the DetachedCriteria is duplicated upon return.
>
> This returns one resultset
> ********************************************************************************
> def results = VviewGlDetailAccount.findAllByCountyNbrAndDistrictNbrAndTransactionIdLikeAndFundCodeLikeAndResourceCodeLikeAndGlAcctCodeNotEqual(
>                countyNbr,
>                districtNbr,
>                transID,
>                fundCode,
>                resCode,
>                '0000')
> ********************************************************************************
>
>
>
> This returns a similar result set except for the glAcctCode
> ********************************************************************************
> def criteria = new DetachedCriteria(VviewGlDetailAccount).build {
>            eq('countyNbr', countyNbr)
>            eq('districtNbr', districtNbr)
>            like('transactionId', transID)
>            ge('transactionDate',beginDate)
>            le('transactionDate',endDate)
>            like('fundCode', fundCode)
>            like('glAcctCode', glAcctCode)
>            ne('glAcctCode', '0000')
>            like('resourceCode', resCode)
>            order('transactionId', 'asc')
>            order('transactionDate', 'asc')
>            order('fundCode', 'asc')
>            order('resourceCode', 'asc')
>            order('glAcctCode', 'asc')
>            firstResult(offset)
>            cache(true)
>            maxResults(max)
>        }
>
>        def results = criteria.list()
>
> Please advise, David.
>
> ---------------------------------------------------------------------
> To unsubscribe from this list, please visit:
>
>   http://xircles.codehaus.org/manage_email
>
>

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email