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

问题描述

我使用的是 Grails 1.2.4.我想知道如何按countDistinct"(降序)和投影中的 groupProperty 进行排序.

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.

这是我的域:

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 术语中，这就是我想要的:

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

一般来说，我想得到一个按产品交易次数(降序)排序的产品列表(或只是产品 ID)

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)

这是我的猜测:

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!

推荐答案

试试这个:

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

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

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.

您可以打开超级有用的休眠 SQL 日志记录来调试此类问题.它将准确地向您展示您的标准是如何转换为 SQL 的.运行时:

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)

或者把它放到你的 Config.groovy 中:

or throw this in your Config.groovy:

environments {
    development {
        log4j = {
            debug 'org.hibernate'
             }
     }
}

使用 countDistinct("id", "transactionCount") 作为投影，order("transactionCount") 将按计数排序.

use countDistinct("id", "transactionCount") as the projection and order("transactionCount") will sort by the count.

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