Criteria Query How-To

[Randall Schulz]

Hi,

I would like to perform the equivalent of this SQL query, which works as
desired precisely as shown here against the actual database created by
GORM from my domain class:


mysql> select distinct resident_folder from job;
+-----------------+
| resident_folder |
+-----------------+
| /rrs            |
| /rrs/PSL        |
+-----------------+
2 rows in set (0.01 sec)


In particular, I cannot seem to make the projections { ... } node work.
Here is the method I defined in an attempt to replicate the foregoing
query:

    public          \
    static          \
    folderNames()
    {
        return Clif.createCriteria().listDistinct {
            fetchMode('residentFolder', FM.EAGER)
            projections { 'residentFolder' }
            order('residentFolder', 'asc')
        }
    }


This variant produces precisely the same results:

    public          \
    static          \
    folderNames()
    {
        return Clif.createCriteria().list {
            fetchMode('residentFolder', FM.EAGER)
            projections { distinct('residentFolder') }
            order('residentFolder', 'asc')
        }
    }



Randall Schulz

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

    http://xircles.codehaus.org/manage_email

[Peter Ledbrook-2]

>    public          \
>    static          \
>    folderNames()
>    {
>        return Clif.createCriteria().listDistinct {
>            fetchMode('residentFolder', FM.EAGER)
>            projections { 'residentFolder' }
>            order('residentFolder', 'asc')
>        }
>    }

projections {
    property("residentFolder")
}

--
Software Engineer
G2One, Inc.
http://www.g2one.com/

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

    http://xircles.codehaus.org/manage_email

[Randall Schulz]

On Wednesday 21 May 2008 04:33, Peter Ledbrook wrote:

> >    public          \
> >    static          \
> >    folderNames()
> >    {
> >        return Clif.createCriteria().listDistinct {
> >            fetchMode('residentFolder', FM.EAGER)
> >            projections { 'residentFolder' }
> >            order('residentFolder', 'asc')
> >        }
> >    }
>
> projections {
>     property("residentFolder")
> }

Thanks.

That brings up more questions:

1) Why do the single quotes I used not work for property names?

2) Why doesn't listDistinct actually limit the result to distinct
values? Does the duplicate removal happen before the projection?

2a) Why doesn't applying the GDK unique() method to the result of list
(or listDistinct) not change the collection? The residentFolder
property is just a plain String


Would it be possible to give diagnostics for invalid constructs such as
the ones I was originally using?


Randall Schulz

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

    http://xircles.codehaus.org/manage_email

[Randall Schulz]

On Wednesday 21 May 2008 06:55, Randall R Schulz wrote:
> ...
>
> 2a) Why doesn't applying the GDK unique() method to the result of
> list (or listDistinct) not change the collection? The residentFolder
> property is just a plain String

Hmmm... I'm not sure why, but that started working after I sent the
mail.

If I save a domain class source file (when running in development mode)
and quickly switch to the browser, could I transiently get requests
handled by the previous version of the code?


Randall Schulz

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

    http://xircles.codehaus.org/manage_email

[Keith Thomas]

I have a variation of this conundrum. Here is my code executing in the console,

groovy> import org.hibernate.criterion.CriteriaSpecification
groovy> def criteria = Sample.createCriteria()
groovy> def dataList = criteria.listDistinct {
groovy>        and {
groovy>              results {
groovy>                ne ('condition', 'suspect')
groovy>             }        
groovy>           }
groovy>             order("id", "asc")
groovy>             maxResults(15)      
groovy>          }
groovy> dataList.size()

Result: 3

The problem I have is that only three records are returned when 83 records match this criteria. If I set maxResults higher you can see the result is that i get all 83 records back,

groovy> import org.hibernate.criterion.CriteriaSpecification
groovy> def criteria = Sample.createCriteria()
groovy> def dataList = criteria.listDistinct {
groovy>        and {
groovy>              results {
groovy>                ne ('condition', 'suspect')
groovy>             }        
groovy>           }
groovy>             order("id", "asc")
groovy>             maxResults(999)      
groovy>          }
groovy> dataList.size()

Result: 83

However, to work with pagination I'd like (expect?) the first code example to return 15 of the 83 Sample records instead of just 3.

Unfortunately this is just the first half of the mess I have created for myself. The second part relates to trying to get back an instance of grails.orm.PagedResultList for my pagination needs. I have two further code examples. In this first one, I get back a paged list, but the distinct projection is ignored and I get back 951 records from a database contained just 83 :(

groovy> import org.hibernate.criterion.CriteriaSpecification
groovy> def criteria = Sample.createCriteria()
groovy> def dataList = criteria.list (max: 999, offset: 0) {
groovy>        and {
groovy>              results {
groovy>                ne ('condition', 'suspect')
groovy>                projections {  
groovy>                  distinct("id")
groovy>                }
groovy>              }            
groovy>           }
groovy>             order("id", "asc")  
groovy>          }
groovy> println dataList.size()
groovy> println dataList.class.name

951
grails.orm.PagedResultList

When I remove (max: 999, offset: 0) the distimct is honored but I cannot figure out how to get back a PagesResultSet,

groovy> import org.hibernate.criterion.CriteriaSpecification
groovy> def criteria = Sample.createCriteria()
groovy> def dataList = criteria.list {
groovy>        and {
groovy>              results {
groovy>                ne ('condition', 'suspect')
groovy>                projections {  
groovy>                  distinct("id")
groovy>                }
groovy>              }            
groovy>           }
groovy>             order("id", "asc")  
groovy>          }
groovy> println dataList.size()
groovy> println dataList.class.name

83
java.util.ArrayList


I've tried so many variants of the Criteria to get this to work my head is spinning (CriteriaSpecification, projections, list, listDistinct, firstResults, maxResults etc etc ) so any insight offered would be most appreciated.

[Keith Thomas]

It seems to me that pagination and getting distinct records are mutually exclusive using Grail's Hibernate Criteria builder. I'm going to try and find time to attach my debugger to the builder and figure this out properly but meantime some sort of confirmation that I'm not insane would be great.

[Keith Thomas]

A day later, a day wiser. It seems my assertion below may be true. See this link for more details,

   http://floledermann.blogspot.com/2007/10/solving-hibernate-criterias-distinct.html

Should I create a Jira recreating the issue or does one - that I have not yet been able to find - already exist?

Keith Thomas wrote:

It seems to me that pagination and getting distinct records are mutually exclusive using Grail's Hibernate Criteria builder. I'm going to try and find time to attach my debugger to the builder and figure this out properly but meantime some sort of confirmation that I'm not insane would be great.

[Peter Ledbrook-2]

2008/5/29 Keith Thomas <keith.thomas@...>:
>
> A day later, a day wiser. It seems my assertion below may be true. See this
> link for more details,
>
>
> http://floledermann.blogspot.com/2007/10/solving-hibernate-criterias-distinct.html
>
> Should I create a Jira recreating the issue or does one - that I have not
> yet been able to find - already exist?

Yes, please raise an issue.

Thanks,

Peter

--
Software Engineer
G2One, Inc.
http://www.g2one.com/

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

    http://xircles.codehaus.org/manage_email

[Darryl Pentz]

Er, so does this mean it is currently not possible to retrieve distinct results when using a Hibernate Criteria query?

- DP

Peter Ledbrook-2 wrote:

2008/5/29 Keith Thomas <[e-mail]>:
>
> A day later, a day wiser. It seems my assertion below may be true. See this
> link for more details,
>
>
> http://floledermann.blogspot.com/2007/10/solving-hibernate-criterias-distinct.html
>
> Should I create a Jira recreating the issue or does one - that I have not
> yet been able to find - already exist?

Yes, please raise an issue.

Thanks,

Peter

--
Software Engineer
G2One, Inc.
http://www.g2one.com/

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

    http://xircles.codehaus.org/manage_email

[Peter Ledbrook-2]

2008/5/30 Darryl Pentz <djpentz@...>:
>
> Er, so does this mean it is currently not possible to retrieve distinct
> results when using a Hibernate Criteria query?

It means "distinct" doesn't work with paging. Works without the paging, though.

Peter

--
Software Engineer
G2One, Inc.
http://www.g2one.com/

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

    http://xircles.codehaus.org/manage_email

[Darryl Pentz]

Ok, but by the 'sounds' of that blog, it's not a problem you can solve in the underlying Grails Criteria implementation... it's an issue with Hibernate itself?

I ask because I can propose to my stakeholders that we can forgo paging on this particular functionality for the short term, if i know the fix will be in a future Grails update. But if it's more serious I'll just need to raise a red flag or two.

thanks,
Darryl

----- Original Message ----
From: Peter Ledbrook <peter@...>
To: user@...
Sent: Friday, May 30, 2008 10:18:40 AM
Subject: Re: [grails-user] Criteria Query How-To

2008/5/30 Darryl Pentz <djpentz@...>:
>
> Er, so does this mean it is currently not possible to retrieve distinct
> results when using a Hibernate Criteria query?

It means "distinct" doesn't work with paging. Works without the paging, though.

Peter

--
Software Engineer
G2One, Inc.
http://www.g2one.com/

---------------------------------------------------------------------
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

[Peter Ledbrook-2]

2008/5/30 Darryl Pentz <djpentz@...>:
> Ok, but by the 'sounds' of that blog, it's not a problem you can solve in the underlying Grails Criteria implementation... it's an issue with Hibernate itself?
>
> I ask because I can propose to my stakeholders that we can forgo paging on this particular functionality for the short term, if i know the fix will be in a future Grails update. But if it's more serious I'll just need to raise a red flag or two.

It's difficult to say at this stage, since I don't think anyone has
had a chance to look at the problem. However, it may be possible to
implement the guy's workaround under the covers, so the user doesn't
need to worry about it. As for your application, you can either
implement the workaround yourself, or fall back to HQL (which
hopefully does work for this case).

Cheers,

Peter

--
Software Engineer
G2One, Inc.
http://www.g2one.com/

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

    http://xircles.codehaus.org/manage_email

[Keith Thomas]

Jira created,

http://jira.codehaus.org/browse/GRAILS-3045

However, once you know what the problem is it is very easy to circumvent - albeit with a possible performance hit - by just asking the HibernateCriteriaBuilder to return distinct records and taking care of the pagination yourself. My quick and dirty home-grown pagination was achieved by nothing more than changing the parameters going into the paginate gsp tag to be values returned by the controller list action and using the subList() method on the collection returned by HibernateCriteriaBuilder.