Many-To-Many relationship and criteria query

[FrugalHorn]

I am a newbie to Grails. So sorry if this is explained earlier. I have two domain classes. Person and Hobby.

class Person
{
        String name
        static hasMany = [hobbies:Hobby]
}

class Hobby
{
        String name
        static hasMany = [persons:Person]
        static belongsTo = Person
}

So now they have a many to many(m:n) relationship. Then I added a few dummy records as persons and hobbies. So how should I write a criteria query to get persons with 'diving' hobby.

My best shot was:
Person.createCriteria().list
{
    and
    {
           hobbies
           {
                eq(name, 'diving')
           }
    }
}

So any help regarding this will be thankful. Where can I find more documentation about the syntax part of writing criteria queries. The documentation in Grails book and site I feel is not adequate.

[Mihai Cazacu-2]

I think your query is fine. You also can try the query version without the "and" part:

[code]
Person.createCriteria().list {
    hobbies {
       eq(name, 'diving')
    }
}
[/code]

[Kamal Govindraj]

Take a look at
Criteria section - that has an example of querying associations.

Person.createCriteria().list
{
      hobbies
      {
          eq('name', 'diving')
      }
}

note the quote around the property name.

Regards,
Kamal

FrugalHorn wrote:

I am a newbie to Grails. So sorry if this is explained earlier. I have two domain classes. Person and Hobby.

class Person
{
        String name
        static hasMany = [hobbies:Hobby]
}

class Hobby
{
        String name
        static hasMany = [persons:Person]
        static belongsTo = Person
}

So now they have a many to many(m:n) relationship. Then I added a few dummy records as persons and hobbies. So how should I write a criteria query to get persons with 'diving' hobby.

My best shot was:
Person.createCriteria().list
{
    and
    {
           hobbies
           {
                eq(name, 'diving')
           }
    }
}

So any help regarding this will be thankful. Where can I find more documentation about the syntax part of writing criteria queries. The documentation in Grails book and site I feel is not adequate.

http://grails.org/doc/1.1.x/guide/5.%20Object%20Relational%20Mapping%20(GORM).html#5.4.2 Criteria

[FrugalHorn]

Thanks guys. It works fine.

Though another scenario and another question around the same case. Say I add following to my Person class:

SortedSet hobbies;

Then I run a query:

Person.createCriteria().list
{
           hobbies
           {
                eq('name', 'diving')
           }
}

My query is successful but why person.hobbies contain only one hobby ie "diving" though in database the person has more than one hobbies. Do I need to add something to my query?

Thanks.

[Mihai Cazacu-2]

http://adhockery.blogspot.com/search/label/criteria%20queries

Try something like this:

[code]
Person.withCriteria {
     createAlias("hobbies", "h")
     eq("h.name", "diving")    
}
[/code]

[FrugalHorn]

Thank you so much. It works smooth as silk. :)

Mihai Cazacu-2 wrote:

http://adhockery.blogspot.com/search/label/criteria%20queries

Try something like this:

[code]
Person.withCriteria {
     createAlias("hobbies", "h")
     eq("h.name", "diving")
}
[/code]