Grails: Projection on many tables?

Question

I have some problems with projection in Grails. Could you please help me review them and suggest solutions for me?

I want to query data on many tables which has many-to-one relationship and projection on some properties on both of them. For example:

class Person {
    int id
    String name
    String address
    static hasMany = [cars : Car]
}

class Car {
   int id
   String brand
   long price
   Person owner
   static belongsTo = [owner : Person]
}

So, how can I use just one query withCriteria (apply projection) to get information of a specified car (include brand, price, and the owner name)? Is it possible to use:

Car.withCriteria {
     projections {
         property("brand")
         property("price")
         property("owner.name")
    }
    eq("id", carId)
}

Can I use a projection to get information of one specified person along with name of all his cars? For example: [01, Perter, 01 Street A, [Mercedes, Toyota, Ducatti]]?
A special situation: (with above Person class)
A person can join many Organization, and an Organization can have one "parent" Organizations (and vice versa, an Organization can have many other depend organizations). But there's a rule: a person just can join only one child organization of a given organization. So with a given organization O and a person P, what is the fastest way to get information of P along with the name of depended organization of O which has P as a member. I prefer to use Grails projection.

Here's data model:
```
class Person {
    int id
    String name
    String address
    static hasMany = [joinedOrgs : Organization] 
}

class Organization {
    int id
    String name
    Organization parentOrg
    static hasMany = [members : Person, childOrgs : Organization]
}
```

I'm a newbie with Grails, and I'd like to understand GORM much more. Thank you so much for your help.

score 9 · Answer 1 · answered Sep 14 '11 at 11:32

9

Try this

def results =  Car.createCriteria().list() { 
    createAlias('owner','owner')
    projections { 
        property('owner.name', 'owner.name') 
        property('brand','brand')
        property('price','price')
    }
}

answered Sep 14 '11 at 11:32

Muhammad Aamir Talib

455
6
18

Why was the `alias` needed? – Alexander Suraphel Jul 08 '15 at 13:33
@AlexanderSuraphel without alias you cannot access `owner.name` directly in `property('owner.name', 'owner.name')` Even you can get above results without alias but you need to write more code for this. see below example `projections { owner { property('name', 'name') } property('brand','brand') property('price','price') }` Please let me know if you still have any confusion, or It was not a right answer for you. Thanks – Muhammad Aamir Talib Oct 15 '15 at 08:15
@AlexanderSuraphel If you can write above code without `createAlias('owner', 'owner')` than may be it is possible in newer versions of grails – Muhammad Aamir Talib Oct 15 '15 at 08:19

score 4 · Answer 2 · answered Jun 28 '11 at 11:41

For (No. 1) I think what you're looking for is explained here

http://grails.1312388.n4.nabble.com/grails-reports-page-multiple-domain-criteria-join-td3510604.html

    def criteria = Company.createCriteria() 
    def results =  criteria.list { 
        projections { 
                property('id', 'company.id') 
                ... 
                POCs{ 
                        property('id', 'poc.id') 
                        property('name', 'poc.name') 
                } 
                software { 
                        productKey { 
                                ... 
                                } 
                } 
                ... 
                order(company.name,'asc') 
        }

the post highlights using .createCriteria()

score 1 · Accepted Answer · edited May 23 '17 at 12:13

For (1) and (2), I don't know if there's a way to do what you want with just 1 criteria query, but the alternative way is just seems to be more simple and natural. For (1):

def car = Car.get(carId)
def owners = car.owner?.name

For (2)

def person = Person.get(personId)
def cars = person.cars*.name

About (3), it's kind of design problem here. Your current domain design doesn't reflect the rule you describe, so it will be hard to maintain that constraint. You may consider mapping a PersonOrganization table and make childrenOrganization a transient property. For example:

class Organization {
    int id
    String name
    Organization parent
    static transients = ['children']
    ...

    Boolean children() {
        def children = Organization.createCriteria().list() { 
            eq ('parent', this) 
        }
        return children
    }
}

After that, you can use a trace-back function like getAllAncestors() to determine all parent-hierarchy of an organization, the look it up in the person-organization list. It seems not the best way, but that's a possible way.

Grails: Projection on many tables?

3 Answers3

Linked