Building criteria queries

classic Classic list List threaded Threaded
19 messages Options
Reply | Threaded
Open this post in threaded view
|

Building criteria queries

Peter Willis-5

How can I build a criteria without a running it, and yet still, be able to add other criterions to it later on. Example:

def c = Domain.createCriteria()

def temp = { 
eq('name', 'peter')
}

I can run the above as c.list(temp) now which is great, but what If I decided to add more constraints to the query..  something like this:

def temp1 = {
eq('email', '[hidden email]')
}

and now be able to run it as c.list(temp, temp1) ?

Obviously the latter doesn't work. But how can accomplish this with something similar? I am sure it's possible somehow.

Cheers, Peter
Reply | Threaded
Open this post in threaded view
|

Re: Building criteria queries

Jeff Scott Brown
On Sun, Jan 10, 2010 at 11:23 AM, Peter Willis
<[hidden email]> wrote:

>
> How can I build a criteria without a running it, and yet still, be able to
> add other criterions to it later on. Example:
> def c = Domain.createCriteria()
>
> def temp = {
>
> eq('name', 'peter')
>
> }
> I can run the above as c.list(temp) now which is great, but what If I
> decided to add more constraints to the query..  something like this:
> def temp1 = {
>
> eq('email', '[hidden email]')
>
> }
> and now be able to run it as c.list(temp, temp1) ?
> Obviously the latter doesn't work. But how can accomplish this with
> something similar? I am sure it's possible somehow.
> Cheers, Peter

I would investigate what you are really trying to accomplish and then
decide if this is really the best way to go about this but that aside,
a way to do specifically what you described is something like this...

def c = Domain.createCriteria()

def temp = {
  eq 'name', 'peter'
}

def temp1 = {
  temp.delegate = delegate
  temp()
  eq 'email', '[hidden email]'
}

c.list(temp1)



jb
--
Jeff Brown
SpringSource
http://www.springsource.com/

Autism Strikes 1 in 166
Find The Cause ~ Find The Cure
http://www.autismspeaks.org/

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

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|

Re: Building criteria queries

Peter Willis-5
Jeff, thanks.. I rewrote that to a more complete method.

def c = Domain.createCriteria()

def q0 = {
 eq 'name', 'peter'
}

def q1 = {
 eq 'approved', true
}

def q2 = {
 le 'time', new Date() - 7
}

Closure join(Object[] queries) {    
    return {
        for(q in queries) {
            q.delegate = delegate
            q()
        }
    }
}

c.list( join(q0, q1, q2) )



"I would investigate what you are really trying to accomplish and then
decide if this is really the best way to go about this"


Is this really a bad approach? I am trying to apply some DRY principles on this one as well.






Reply | Threaded
Open this post in threaded view
|

Re: Building criteria queries

Peter Willis-5
Sorry, accidentally sent out too early.

On:
"I would investigate what you are really trying to accomplish and then
decide if this is really the best way to go about this"


Is this really a bad approach? I am trying to apply some DRY principles on this one as well.
If I write a complex query in a method and would like to reuse that part + with some additional requirements, 
the standard way promotes that you call the same method and use some if blocks to determine when to place that additional part. Not very sexy.

Not can I separate reusable parts of my queries and use them later on, without the need to have one large criteria query. Is this not a good approach? Why if not?

Thank you, Peter
Reply | Threaded
Open this post in threaded view
|

Re: Building criteria queries

Robert Fletcher
You can do this with named queries in Grails 1.2 as I understand it.
You can define the named query on the class then when using it further
restrict it by using dynamic findAll type queries. See:
http://grails.org/doc/latest/ref/Domain%20Classes/namedQueries.html

It's not documented on that page but IIRC Graeme showed chaining
additional criteria onto a named query during his talk at Groovy &
Grails Exchange.

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

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|

Re: Building criteria queries

Peter Willis-5


On Sun, Jan 10, 2010 at 8:37 PM, Robert Fletcher <[hidden email]> wrote:
You can do this with named queries in Grails 1.2 as I understand it.
You can define the named query on the class then when using it further
restrict it by using dynamic findAll type queries. See:
http://grails.org/doc/latest/ref/Domain%20Classes/namedQueries.html


Looks promising. I am still on version <1.1 plus this seems pretty restricted in it's application. Only on dynamic finders?
The example shows that you can reuse only one namedQueries. It doesn't demonstrate how you can combine multiple queries. It seems to be either recentPublications or oldPublicationsLargerThan.

I am pretty satisfied with the method above actually. I think it should be like this though :

    Closure join(Object[] queries) {    
        return {
            for(q in queries) {
                def temp = q.delegate
                q.delegate = delegate
                q()
                q.delegate = temp   // Restore
            }
        }
    }

Cheers, Peter
 


It's not documented on that page but IIRC Graeme showed chaining
additional criteria onto a named query during his talk at Groovy &
Grails Exchange.

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

   http://xircles.codehaus.org/manage_email



Reply | Threaded
Open this post in threaded view
|

Re: Building criteria queries

Jeff Scott Brown
In reply to this post by Robert Fletcher
On Sun, Jan 10, 2010 at 12:37 PM, Robert Fletcher
<[hidden email]> wrote:
>
>
> It's not documented on that page but IIRC Graeme showed chaining
> additional criteria onto a named query during his talk at Groovy &
> Grails Exchange.
>

Hmmm... that doesn't sound right.  Can you recall more specifically
what you think you saw with respect to that?

You can append things like dynamic finders and findWhere (per the
docs) but I sense that isn't what you are talking about.



jb
--
Jeff Brown
SpringSource
http://www.springsource.com/

Autism Strikes 1 in 166
Find The Cause ~ Find The Cure
http://www.autismspeaks.org/

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

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|

Re: Building criteria queries

Robert Fletcher
I'm probably misremembering but I thought he showed chaining a
criteria closure (as per withCriteria) on the end of a named query in
place of a list or find* type call.

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

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|

Re: Building criteria queries

Jeff Scott Brown
On Sun, Jan 10, 2010 at 1:39 PM, Robert Fletcher
<[hidden email]> wrote:
> I'm probably misremembering but I thought he showed chaining a
> criteria closure (as per withCriteria) on the end of a named query in
> place of a list or find* type call.

No, I don't think you can do that.  However, that seems like a
reasonable thing that one might want to do and would be pretty easy to
add.

If folks think that would be useful, please file a jira requesting the
new feature.



jb
--
Jeff Brown
SpringSource
http://www.springsource.com/

Autism Strikes 1 in 166
Find The Cause ~ Find The Cure
http://www.autismspeaks.org/

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

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|

Re: Building criteria queries

Moe-25
Robert, perhaps it was something like this: 

List cats = session.createCriteria(Cat.class)
     .add( Restrictions.like("name", "Iz%") )
     .add( Restrictions.gt( "weight", new Float(minWeight) ) )
     .addOrder( Order.asc("age") )
     .list();

Hibernates criteria seems to offer an extensive way to do the above mentioned stuff. Rather than calling list() you can get just the Criteria object, add more stuff and then call.

With Grails it would be done like this:

import org.hibernate.criterion.Restrictions as R

def criteria = Domain.createCriteria()
criteria = criteria.add( R.eq('name', 'moe') )
criteria = criteria.add( R.gt('age', 10) )
criteria.list()

// Or
def criteria = Domain.createCriteria()
    .add( R.eq('name', 'moe') )
    .add( R.gt('age', 10) )
    .list()


It's a bit sad that you have to import and use the Restrictions class. Not sure if there is a better Grails way to do this. 
Grails should offer similar methods (add, addOrder..) that takes a closure and bypass the Restriction class.

/ Moe


On Sun, Jan 10, 2010 at 9:49 PM, Jeff Brown <[hidden email]> wrote:
On Sun, Jan 10, 2010 at 1:39 PM, Robert Fletcher
<[hidden email]> wrote:
> I'm probably misremembering but I thought he showed chaining a
> criteria closure (as per withCriteria) on the end of a named query in
> place of a list or find* type call.

No, I don't think you can do that.  However, that seems like a
reasonable thing that one might want to do and would be pretty easy to
add.

If folks think that would be useful, please file a jira requesting the
new feature.



jb
--
Jeff Brown
SpringSource
http://www.springsource.com/

Autism Strikes 1 in 166
Find The Cause ~ Find The Cure
http://www.autismspeaks.org/

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

   http://xircles.codehaus.org/manage_email



Reply | Threaded
Open this post in threaded view
|

Re: Building criteria queries

Jeff Scott Brown
On Sun, Jan 10, 2010 at 6:30 PM, Moe <[hidden email]> wrote:

> Robert, perhaps it was something like this:
>
> List cats = session.createCriteria(Cat.class)
>      .add( Restrictions.like("name", "Iz%") )
>      .add( Restrictions.gt( "weight", new Float(minWeight) ) )
>      .addOrder( Order.asc("age") )
>      .list();
>
> https://www.hibernate.org/hib_docs/v3/api/org/hibernate/Criteria.html#add(org.hibernate.criterion.Criterion)
> Hibernates criteria seems to offer an extensive way to do the above
> mentioned stuff. Rather than calling list() you can get just the Criteria
> object, add more stuff and then call.
> With Grails it would be done like this:
> import org.hibernate.criterion.Restrictions as R
>
> def criteria = Domain.createCriteria()
> criteria = criteria.add( R.eq('name', 'moe') )
> criteria = criteria.add( R.gt('age', 10) )
> criteria.list()
> // Or
> def criteria = Domain.createCriteria()
>     .add( R.eq('name', 'moe') )
>     .add( R.gt('age', 10) )
>     .list()
>
> It's a bit sad that you have to import and use the Restrictions class. Not
> sure if there is a better Grails way to do this.

Isn't this what you want?...

Domain.withCriteria {
  eq 'name', 'moe'
  gt 'age', 10
}



jb

--
Jeff Brown
SpringSource
http://www.springsource.com/

Autism Strikes 1 in 166
Find The Cause ~ Find The Cure
http://www.autismspeaks.org/

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

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|

Re: Building criteria queries

Moe-25

On Mon, Jan 11, 2010 at 2:54 AM, Jeff Brown <[hidden email]> wrote:
On Sun, Jan 10, 2010 at 6:30 PM, Moe <[hidden email]> wrote:
> Robert, perhaps it was something like this:
>
> List cats = session.createCriteria(Cat.class)
>      .add( Restrictions.like("name", "Iz%") )
>      .add( Restrictions.gt( "weight", new Float(minWeight) ) )
>      .addOrder( Order.asc("age") )
>      .list();
>
> https://www.hibernate.org/hib_docs/v3/api/org/hibernate/Criteria.html#add(org.hibernate.criterion.Criterion)
> Hibernates criteria seems to offer an extensive way to do the above
> mentioned stuff. Rather than calling list() you can get just the Criteria
> object, add more stuff and then call.
> With Grails it would be done like this:
> import org.hibernate.criterion.Restrictions as R
>
> def criteria = Domain.createCriteria()
> criteria = criteria.add( R.eq('name', 'moe') )
> criteria = criteria.add( R.gt('age', 10) )
> criteria.list()
> // Or
> def criteria = Domain.createCriteria()
>     .add( R.eq('name', 'moe') )
>     .add( R.gt('age', 10) )
>     .list()
>
> It's a bit sad that you have to import and use the Restrictions class. Not
> sure if there is a better Grails way to do this.

Isn't this what you want?...

Domain.withCriteria {
 eq 'name', 'moe'
 gt 'age', 10
}



Well, no.. I was suggesting a solution to Peters question.

def method(){
def criteria = Domain.createCriteria()
.add( R.eq('name', 'moe') )
.add( R.gt('age', 10) )
return criteria
}

def method1(){

def criteria = method()
.add( R.eq( 'gender', 'male') )

.list().each {
...
}

}

method1()

Is this not what he was asking for?
 


jb

--
Jeff Brown
SpringSource
http://www.springsource.com/

Autism Strikes 1 in 166
Find The Cause ~ Find The Cure
http://www.autismspeaks.org/

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

   http://xircles.codehaus.org/manage_email



Reply | Threaded
Open this post in threaded view
|

Re: Building criteria queries

Moe-25
Sorry, maybe I misunderstood you. The Grails way of building Criteria queries, force you to immediately run the query. Either withCriteria or createCriteria. 


From: http://www.grails.org/Hibernate+Criteria+Builder
If you invoke the builder with no method name such as:
c { … }
defaults to
c.list { … }

So there is no way to use the Grails criteria way to build a query (using a Closure) and actually get back a Hibernate Criteria.  If that was to be return it could have been easily passed, along with some new stuff to add to it.

(There is an getInstance method in http://grails.org/doc/1.1.x/api/grails/orm/HibernateCriteriaBuilder.html#getInstance() but how can this even be called if a closure has been used earlier to build the query?)

/ Moe

On Mon, Jan 11, 2010 at 3:06 AM, Moe <[hidden email]> wrote:

On Mon, Jan 11, 2010 at 2:54 AM, Jeff Brown <[hidden email]> wrote:
On Sun, Jan 10, 2010 at 6:30 PM, Moe <[hidden email]> wrote:
> Robert, perhaps it was something like this:
>
> List cats = session.createCriteria(Cat.class)
>      .add( Restrictions.like("name", "Iz%") )
>      .add( Restrictions.gt( "weight", new Float(minWeight) ) )
>      .addOrder( Order.asc("age") )
>      .list();
>
> https://www.hibernate.org/hib_docs/v3/api/org/hibernate/Criteria.html#add(org.hibernate.criterion.Criterion)
> Hibernates criteria seems to offer an extensive way to do the above
> mentioned stuff. Rather than calling list() you can get just the Criteria
> object, add more stuff and then call.
> With Grails it would be done like this:
> import org.hibernate.criterion.Restrictions as R
>
> def criteria = Domain.createCriteria()
> criteria = criteria.add( R.eq('name', 'moe') )
> criteria = criteria.add( R.gt('age', 10) )
> criteria.list()
> // Or
> def criteria = Domain.createCriteria()
>     .add( R.eq('name', 'moe') )
>     .add( R.gt('age', 10) )
>     .list()
>
> It's a bit sad that you have to import and use the Restrictions class. Not
> sure if there is a better Grails way to do this.

Isn't this what you want?...

Domain.withCriteria {
 eq 'name', 'moe'
 gt 'age', 10
}



Well, no.. I was suggesting a solution to Peters question.

def method(){
def criteria = Domain.createCriteria()
.add( R.eq('name', 'moe') )
.add( R.gt('age', 10) )
return criteria
}

def method1(){

def criteria = method()
.add( R.eq( 'gender', 'male') )

.list().each {
...
}

}

method1()

Is this not what he was asking for?
 


jb

--
Jeff Brown
SpringSource
http://www.springsource.com/

Autism Strikes 1 in 166
Find The Cause ~ Find The Cure
http://www.autismspeaks.org/

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

   http://xircles.codehaus.org/manage_email




Reply | Threaded
Open this post in threaded view
|

Re: Building criteria queries

Jeff Scott Brown
On Sun, Jan 10, 2010 at 7:16 PM, Moe <[hidden email]> wrote:
> Sorry, maybe I misunderstood you. The Grails way of building Criteria
> queries, force you to immediately run the query. Either withCriteria or
> createCriteria.
>

That isn't correct.  The createCriteria method returns a criteria builder.



jb
--
Jeff Brown
SpringSource
http://www.springsource.com/

Autism Strikes 1 in 166
Find The Cause ~ Find The Cure
http://www.autismspeaks.org/

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

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|

Re: Building criteria queries

Moe-25


On Mon, Jan 11, 2010 at 3:44 AM, Jeff Brown <[hidden email]> wrote:
On Sun, Jan 10, 2010 at 7:16 PM, Moe <[hidden email]> wrote:
> Sorry, maybe I misunderstood you. The Grails way of building Criteria
> queries, force you to immediately run the query. Either withCriteria or
> createCriteria.
>

That isn't correct.  The createCriteria method returns a criteria builder.


Yes, your right. I was referring to what happens after you pass the closure that build the query. You have to run it.

 


jb
--
Jeff Brown
SpringSource
http://www.springsource.com/

Autism Strikes 1 in 166
Find The Cause ~ Find The Cure
http://www.autismspeaks.org/

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

   http://xircles.codehaus.org/manage_email



Reply | Threaded
Open this post in threaded view
|

Re: Building criteria queries

ld@ldaley.com

On 11/01/2010, at 11:50 AM, Moe wrote:

> Yes, your right. I was referring to what happens after you pass the closure that build the query. You have to run it.

Yes, but that doesn't preclude you from doing what I think you want. I use this technique a lot…

def getBaseRestriction() {
        return {
                like('name', '%whatever%')
        }
}

def base = getBaseRestriction()

Person.withCriteria {
        base.delegate = delegate
        base()

        eq('age', 16)
}

You should be able to use some kind of variant on that technique to achieve what you want.
---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|

Re: Building criteria queries

Nicolás Dijkstra
In reply to this post by Peter Willis-5
Hi, I'm sorry if my suggestion comes a bit late and out of context but i wish you find it helpful. This is how I worked out a problem similar to yours. I wanted to have some reusable filtering but with different criteria behaviours (with or without counting records) and this is what i did:

  Closure filter = { countRows
    or {
      ilike('firstName', "${filter}%")
      ilike('lastName', "${filter}%")
    }
    if(countRows) {
      projections {
        rowCount()
      }
    }
  }

  def result = Patient.createCriteria().list(
      offset:params.start, max:params.limit, sort:params.sort, order:params.dir,
      filter.curry(false) // call criteria closure WITHOUT record
 
)
  def totalCount = Paciente.createCriteria().get(filter.curry(true)) // call criteria closure WITH record count

Criteria builder receives a closure as a parameter. Therefore you can declare the reusable closure with all the possible behaviours and then pass parameters to it in order to achieve the result you want in any situation.

Regards Nicolás

On Sun, Jan 10, 2010 at 2:23 PM, Peter Willis <[hidden email]> wrote:

How can I build a criteria without a running it, and yet still, be able to add other criterions to it later on. Example:

def c = Domain.createCriteria()

def temp = { 
eq('name', 'peter')
}

I can run the above as c.list(temp) now which is great, but what If I decided to add more constraints to the query..  something like this:

def temp1 = {
eq('email', '[hidden email]')
}

and now be able to run it as c.list(temp, temp1) ?

Obviously the latter doesn't work. But how can accomplish this with something similar? I am sure it's possible somehow.

Cheers, Peter

Reply | Threaded
Open this post in threaded view
|

Re: Building criteria queries

Peter Willis-5
Nicolas, the idea was to separate one criteria query into several parts and avoid having if blocks in one major criteria.

The way Moe described is a good solution but it's unfortunate that it moves away from Grails way of building queries ( not that Hibernates are all that bad) 
Ultimately, the best way I found was to use Jeffs, and Lukes way and turn it into a general function:

Closure join(Object[] queries) {
      return {
        for(q in queries) {
          def temp = q.delegate
          q.delegate = delegate
          q()
          q.delegate = temp   // Restore
        }
      }
}

def q0 = { eq('name', 'peter') }
def q1 = { eq('email', '[hidden email]) }

def finalQuery = join(q0, q1) {
maxResults(10)
}

Domain.withCriteria (finalQuery).each { ... }

Cheers, Peter


2010/1/11 Nicolás Dijkstra <[hidden email]>
Hi, I'm sorry if my suggestion comes a bit late and out of context but i wish you find it helpful. This is how I worked out a problem similar to yours. I wanted to have some reusable filtering but with different criteria behaviours (with or without counting records) and this is what i did:

  Closure filter = { countRows
    or {
      ilike('firstName', "${filter}%")
      ilike('lastName', "${filter}%")
    }
    if(countRows) {
      projections {
        rowCount()
      }
    }
  }

  def result = Patient.createCriteria().list(
      offset:params.start, max:params.limit, sort:params.sort, order:params.dir,
      filter.curry(false) // call criteria closure WITHOUT record
 
)
  def totalCount = Paciente.createCriteria().get(filter.curry(true)) // call criteria closure WITH record count

Criteria builder receives a closure as a parameter. Therefore you can declare the reusable closure with all the possible behaviours and then pass parameters to it in order to achieve the result you want in any situation.

Regards Nicolás


On Sun, Jan 10, 2010 at 2:23 PM, Peter Willis <[hidden email]> wrote:

How can I build a criteria without a running it, and yet still, be able to add other criterions to it later on. Example:

def c = Domain.createCriteria()

def temp = { 
eq('name', 'peter')
}

I can run the above as c.list(temp) now which is great, but what If I decided to add more constraints to the query..  something like this:

def temp1 = {
eq('email', '[hidden email]')
}

and now be able to run it as c.list(temp, temp1) ?

Obviously the latter doesn't work. But how can accomplish this with something similar? I am sure it's possible somehow.

Cheers, Peter


Reply | Threaded
Open this post in threaded view
|

Re: Building criteria queries

Jeff Scott Brown
In reply to this post by Robert Fletcher
On Sun, Jan 10, 2010 at 1:39 PM, Robert Fletcher
<[hidden email]> wrote:
> I'm probably misremembering but I thought he showed chaining a
> criteria closure (as per withCriteria) on the end of a named query in
> place of a list or find* type call.
>

http://jira.codehaus.org/browse/GRAILS-5796 was added today.



jb
--
Jeff Brown
SpringSource
http://www.springsource.com/

Autism Strikes 1 in 166
Find The Cause ~ Find The Cure
http://www.autismspeaks.org/

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

    http://xircles.codehaus.org/manage_email