在Grails中计算一对一的关系 [英] Count one-to-one relationship in grails

问题描述

我在查询时遇到问题。我想要做的是通过MyDomainB中的查询访问MyDomainA中的String代码中的数据。这些表格之间的关系是单向的一对一。

有可能使用gorm的方式来做到这一点??

域A：

 类LicenceType {
 
字符串代码
字符串描述
双价
 
静态限制= {
} 
 
} 
  

TABLE DOMAIN A

 代码说明
 A这是A 
 B这是B 
 C这是C 
  

域B :(有单向关系）

  class VoiceUser {
 
 LicenceType licenceType 
 
字符串用户名
字符串电子邮件
字符串名称ID 
  

}

TABLE DOMAIN B

 用户
 1 
 2 
 3 
 4 
  

我想要做的是知道有多少用户具有相同的代码（代码是一列的DomainA和两个表我有一个单向关系，正如我前面所指出的那样）。

这就是我想要做的那是错误的...
控制器：

  def resulta = VoiceUser.executeQuery（'SELECT a.code，COUNT（b.nameID）FROM VoiceUser AS b INNER JOIN b.licenceType AS ）
 
 def resultCount = resulta [0] 
  

<

 代码为A = 2的用户
拥有代码的用户B = 2 
代码为C = o 
的用户 

解决方案

<诀窍是通过代码和 count（）来组执行 在用户上。您可以使用HQL或标准查询来完成此操作。



HQL

以下是HQL中的一个例子：

  VoiceUser.executeQuery（'SELECT licence.code，COUNT（user）FROM VoiceUser AS user INNER JOIN user.licenceType AS license GROUP BY licence.code'）
  

如果您熟悉SQL，那么大多数情况下都应该马上解决。一个重要的区别是加入域类的语法。 HQL处理域类而不是表。

标准查询

这里是等效的标准查询。 p>

  VoiceUser.withCriteria {
预测{
 licenceType {
 groupProperty（'code'）
 
 
 count（'id'）
} 
} 
  

其他查询

上面显示的查询返回一个列表< List> ，如下所示：

  [
 ['A'，2]，
 ['B'，2]，
 ['C'，0] 
] 
  

如果您提供 LicenceType （或其代码）作为查询的输入，那么您可以获得 LicenceType 的计数。例如，以下是检索许可码A的用户数的示例。

HQL

  def result = VoiceUser.executeQuery（'SELECT COUNT（user）FROM VoiceUser AS user INNER JOIN user.licenceType AS licence where licence.code =：code'，[code：'A']）[ 0] 
  

标准查询

'A'）
} 
 
预测{
 count（'id'）
} 
} 
  

< h1>其他资源

我有一个系列文章，其中详细解释了HQL，标准以及查询的位置;如如何使用投影和连接。随时检查出来。

I have a problem doing a query. That I want to do is access with a query to the data in my "String code" in MyDomainA through a query from MyDomainB. The relationship between the tables is unidirectional one to one.

Is possible use a gorm way to do this??

Domain A:

class LicenceType {

    String code
    String description
    Double price

    static constraints = {
    }

}

TABLE DOMAIN A

code description
A    this is A
B    this is B
C    this is C

Domain B: (have the unidirectional relationship)

class VoiceUser {

LicenceType licenceType

String username
String email
String nameID

}

TABLE DOMAIN B

User 
1    
2    
3    
4  

That I want to do is know how many users have the same code(code is a column of DomainA and both tables have a unidirectional relationship as I indicated before).

This is that I'm trying to do that is wrong... Controller:

        def resulta = VoiceUser.executeQuery('SELECT a.code, COUNT(b.nameID) FROM VoiceUser AS b INNER JOIN b.licenceType AS a GROUP BY a.code')

def resultCount = resulta[0]

This is some example result that I hope...

Users with code A = 2
Users with code B = 2
Users with code C = o

解决方案

The trick is to do a group by on the code and a count() on the user. You can do this using either HQL or a criteria query.

HQL

Here's an example in HQL:

VoiceUser.executeQuery('SELECT licence.code, COUNT(user) FROM VoiceUser AS user INNER JOIN user.licenceType AS licence GROUP BY licence.code')

If you're familiar with SQL, most of this should make sense right away. An important difference is the syntax for joining domain classes. HQL deals with domain classes, not tables.

Criteria query

And here's the equivalent criteria query.

VoiceUser.withCriteria {
    projections {        
        licenceType {
            groupProperty('code')
        }

        count('id')
    }
}

Alternative queries

The queries shown above return a List<List> like this:

[
    ['A', 2], 
    ['B', 2], 
    ['C', 0]
]

If you provide a LicenceType (or its code) as input to the query, then you can get the count for just that LicenceType. For instance, here are examples which retrieve the user count for licence code 'A'.

HQL

def result = VoiceUser.executeQuery('SELECT COUNT(user) FROM VoiceUser AS user INNER JOIN user.licenceType AS licence WHERE licence.code = :code', [code: 'A'])[0]

Criteria query

def result = VoiceUser.createCriteria().get {
    licenceType {
        eq('code', 'A')
    }

    projections {        
        count('id')
    }
}

Additional resources

I've got a series of articles which explain HQL, criteria, and where queries in detail; such as how to use projections and joins. Feel free to check them out.

这篇关于在Grails中计算一对一的关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！