无法获得“计数”和“groupBy”与Grails DetachedCriteria [英] Can't get "count" and "groupBy" with Grails DetachedCriteria

问题描述

  TourIndex {
长tourId 
我有一个用于查询的域类。字符串国家
字符串位置
 int可用性
 // ...更多字段
} 
  

如果动态条件构建器基于一系列基本上可以执行的配置进行搜索，则我们使用一系列：

  def detachedCriteria = new DetachedCriteria（TourSearchIndex）
 
 detachedCriteria = detachedCriteria.build {eq（'country'，'AR'）} 
 detachedCriteria = detachedCriteria.build { eq（'location'，'salta'）} 
  

我想重复使用这个逻辑并获得可用过滤器及其各自的结果形式

  [['location1'，'3']，['location2'，'5 ']] 
  

我最疯狂的猜测是：

  detachedCriteria = detachedCriteria.build {
预测{
 property（'location'）
 countDistinct（'tourId'）
} 
} 
  

但这会导致一个非常明显的错误

 由org.h2引发。 jdbc.JdbcSQLException：列THIS_.LOCATION必须位于GROUP BY列表中; SQL语句：
选择this_.location作为y0_，将count（不同于this_.tour_id）作为y1_ from tour_search_index this_ where this_.country =？ [90016-173] 
  

使用createCriteria，我有一种方法可以根据如何使用Grails标准将订单子句中的属性分组

  def criteria = TourSearchIndex.createCriteria（）
 
 def result = criteria.list {
预测{
 groupProperty（'location'）
 countDistinct（'id'，'idDistinct'）
} 
 order（'idDistinct'，'desc'）
 } 
  

但我想使用已使用应用程序其余部分的DetachedCriteria，有没有解决方法为了这？我认为缺少的是DetachedCriteria内部类中的DetachedProjection中的groupProperty。

感谢您提前致谢。

解决方案

对于DetachedCriteria，语法有点不同。

  detachedCriteria = detachedCriteria.build {
 
} .projections {
 property（'location '）
 countDistinct（'tourId'）
} .list（）
  

I have a domain class that I use for querying .

TourIndex{
     Long tourId
     String country
     String location
     int availability
     // ... more fields
}

We use a series if "dynamic" criteria builders for searching based on a series of configurations that basically results in performing:

def detachedCriteria = new DetachedCriteria(TourSearchIndex)

detachedCriteria = detachedCriteria.build { eq('country','AR') }
detachedCriteria = detachedCriteria.build { eq('location','salta') }

I want to reutilize this logic and get the available filters with their respective results in the form

[['location1', '3'], ['location2', '5']]

My wildest guess was:

detachedCriteria = detachedCriteria.build{
  projections{
    property('location')
    countDistinct('tourId')
  }
}

But this results in a very obvious error

Caused by: org.h2.jdbc.JdbcSQLException: Column "THIS_.LOCATION" must be in the GROUP BY list; SQL statement:
select this_.location as y0_, count(distinct this_.tour_id) as y1_ from tour_search_index this_ where this_.country=? [90016-173]

With createCriteria I have a way to get the count distinct, according to How to Group property in Order Clause using Grails Criteria

def criteria = TourSearchIndex.createCriteria()

def result = criteria.list {
  projections{
    groupProperty('location')
    countDistinct('id','idDistinct')
  }
  order('idDistinct','desc') 
}

But I want to use the DetachedCriteria that already uses the rest of the application, is there a workaround for this? What I think is missing is the "groupProperty" in the DetachedProjection in DetachedCriteria's inner class.

Thanks in advance.

解决方案

For DetachedCriteria, the syntax is a bit different.

detachedCriteria = detachedCriteria.build{

}.projections{
    property('location')
    countDistinct('tourId')
}.list()

这篇关于无法获得“计数”和“groupBy”与Grails DetachedCriteria的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！