Grails：如何在多个表上进行投影？

Question

我在Grails中遇到了一些投影问题。你能帮我审查一下并为我提供解决方案吗？

1. 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)
   }
   

2. 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]]?

3. 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]
   }
   

我是一个Grails的新手，我想更深入地了解GORM。非常感谢您的帮助。

- Đinh Hồng Châu

Answer 1

试试这个

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

- Muhammad Aamir Talib

---

为什么需要使用“alias”？ - Alexander Suraphel

@AlexanderSuraphel，如果没有别名，您无法直接访问property('owner.name'，'owner.name')中的owner.name。 即使您可以在没有别名的情况下获得上述结果，但您需要编写更多的代码。请参见以下示例 projections { owner { property('name', 'name') } property('brand','brand') property('price','price') } 如果您仍然有任何疑问，或者这不是您要的答案，请告诉我。 谢谢 - Muhammad Aamir Talib

@AlexanderSuraphel 如果你能在不使用createAlias('owner', 'owner')的情况下编写上述代码，那么在较新版本的grails中可能是有可能的。 - Muhammad Aamir Talib

Answer 2

我认为您需要的是这里解释的内容：

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') 
        }

本文重点介绍如何使用.createCriteria() 方法。

- Daxon

Answer 3

对于（1）和（2），我不知道是否有一种方法可以只使用一个条件查询来实现您想要的功能，但另一种替代方式似乎更简单自然。对于（1）：

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

对于（2）

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

关于（3），这里存在一种设计问题。您当前的域设计并未反映您所描述的规则，因此维护该约束将会很困难。您可以考虑映射一个PersonOrganization表，并使childrenOrganization成为{{link1：瞬态属性}}。例如：

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

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

之后，您可以使用类似getAllAncestors()的追溯函数来确定组织的所有父层次结构，然后在人员-组织列表中查找它。这似乎不是最好的方法，但这是一种可能的方法。

- Hoàng Long