在Grails标准中使用groupProperty和countDistinct [英] Using groupProperty and countDistinct in Grails Criteria

问题描述

我正在使用Grails 1.2.4。我想知道如何在预测中按countDistinct（降序）和groupProperty排序。

以下是我的域名：

  class Transaction {
 
 static belongsTo = [customer：Customer，product：Product] 
 
 Date transactionDate = new Date（）
 
 static constraints = {
 transactionDate（blank：false）
} 
 
} 
 
 class Product {
 
 String productCode 
 
 static constraints = {
 productCode（blank：false）
} 
} 
  

在MySQL中，这是我想要的：

  select 
 product_id，
 count（product_id）
 from 
 transaction 
 group by 
 product_id 
 order by 
 count（product_id）desc 
  

总的来说，我希望得到产品（或者仅仅是产品ID）按照反式的数量排序行动产品有（降序）

这是我的猜测：

  def c = Transaction.createCriteria（）def transactions = c.list {
 projection {
 groupProperty（product）
 countDistinct（product）
} 
 maxResults（pageBlock）
 firstResult（pageIndex）} 
 
 def products = transactions.collect {it [0]} 
  

但它没有给我预期的结果。任何领先的这将是高度赞赏。谢谢！

解决方案

试试这个：

  def c = Transaction.createCriteria（）
 def transactions = c.list {
 projections {
 groupProperty（product）
 countDistinct（id）
 
 maxResults（pageBlock）
 firstResult（pageIndex）
} 
  

您的标准查询实际上相当于：

  select 
 product_id，
 count（* * distinct ** product_id）
 from 
 transaction 
 group by 
 product_id 
 order by 
 count（product_id）desc 
  

注意不同。您想要计算每个产品的不同交易数量，因此计算交易ID（或任何交易属性）更有意义。产品id恰好在没有distinct子句的情况下工作。

您可以打开超级有用的hibernate SQL日志记录来调试这类问题。它会向你显示你的标准是如何转换成SQL的。在运行时：

  org.apache.log4j.Logger.getLogger（org.hibernate）.setLevel（org.apache。 log4j.Level.DEBUG）
  

或者在你的Config.groovy中抛出：

  environments {
 development {
 log4j = {
 debug'org.hibernate'
} 
 
 
 
 
 $ b编辑：使用 countDistinct（id ，transactionCount）作为投影并且 order（transactionCount）将按计数排序。
 
I'm using Grails 1.2.4. I would like to know on how can I sort by "countDistinct" (descending) and with groupProperty inside a projections.

Here are my domains:
class Transaction {

    static belongsTo = [ customer : Customer, product : Product ]

    Date transactionDate = new Date()

    static constraints = {
        transactionDate(blank:false)    
    }

}

class Product {

    String productCode

    static constraints = {
        productCode(blank:false)    
    }
}
In MySQL terms, this is what I want:
select 
    product_id,
    count(product_id)
from
    transaction
group by
    product_id
order by
    count(product_id) desc
In general term, I would like to get a list of products (or just product id) sorted by the number of transactions a product had (descending)

This is my guess:
def c = Transaction.createCriteria() def transactions = c.list {
    projections {
        groupProperty("product")
        countDistinct("product")
    }
    maxResults(pageBlock)
    firstResult(pageIndex) }

def products = transactions.collect { it[0] }
But it doesn't give my expected result. Any lead on this will be highly appreciated. Thanks!
解决方案
Try this:
def c = Transaction.createCriteria() 
def transactions = c.list {
    projections {
        groupProperty("product")
        countDistinct("id")
    }
    maxResults(pageBlock)
    firstResult(pageIndex)
}
Your criteria query is actually equivalent to:
select 
    product_id,
    count(**distinct** product_id)
from
    transaction
group by
    product_id
order by
    count(product_id) desc
Note the distinct.  You want to count the number of distinct transactions per product, so counting the transaction id (or any transaction property) makes more sense.  Product id just happens to work without the distinct clause.

You can turn on the super useful hibernate SQL logging for debugging this kind of issue.  It will show you exactly how your criterias are being transformed into SQL.  At runtime: 
org.apache.log4j.Logger.getLogger("org.hibernate").setLevel(org.apache.log4j.Level.DEBUG)
or throw this in your Config.groovy:
environments {
    development {
        log4j = {
            debug 'org.hibernate'
             }
     }
}
EDIT: use countDistinct("id", "transactionCount") as the projection and order("transactionCount") will sort by the count.

                        
这篇关于在Grails标准中使用groupProperty和countDistinct的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！