java - 聚合 Spring Data Couchbase 存储库方法 - 如何查询给定属性的所有唯一值的列表?
问题描述
我无法弄清楚如何在 Spring-Data-Couchbase 的存储库中运行一个非常简单的聚合查询。我在用着org.springframework.data.spring-data-couchbase:3.2.0.RELEASE
假设我有一个名为 Organism 的存储桶,其属性如下:
SpeciesId SpeciesName SpeciesDesc OrderId OrderDesc ClassId ClassDesc
1 Human H sapiens 21 Primates 31 Mammalia
2 Chimpanzee P troglodytes 21 Primates 31 Mammalia
3 Fruit fly D melanogaster 22 Diptera 32 Insecta
我想编写一个查询,允许我搜索唯一的订单或类;在 SQL 中,查询如下所示:
SELECT OrderId, OrderDesc FROM Organism WHERE OrderDesc like 'Pri%' GROUP BY OrderId, OrderDesc
SELECT ClassId, ClassDesc FROM Organism WHERE ClassDesc like 'Mam%' GROUP BY ClassId, ClassDesc
我将如何在 spring-data-couchbase 存储库中创建这些查询?
到目前为止,我已经尝试了大约一千种不同的方法,但都没有奏效。首先,我尝试向存储库添加不同的查询:
public interface Order {
public Integer getOrderId();
public String getOrderDesc();
}
@N1qlPrimaryIndexed
@ViewIndexed(designDoc = "organism")
public interface OrganismRepository extends
CouchbasePagingAndSortingRepository<Organism, String> {
Optional<List<Order>> findDistinctByOrderDescStartingWith(String orderDesc);
}
接下来我尝试使用@Query 注释:
//Exception - Unable to retrieve enough metadata for N1QL to entity mapping, have you selected _ID and _CAS?
@Query("SELECT orderDesc FROM #{#n1ql.bucket} WHERE orderDesc like 'Pri%' AND #{#n1ql.filter}")
Optional<List<String>> cantSelectPropertyBecauseMissingMetadata();
//Exception - Unable to execute query due to the following n1ql errors:
// {"msg":"Expression must be a group key or aggregate: (meta(`organism`).`id`)","code":4210}
@Query("#{#n1ql.selectEntity} where #{#n1ql.filter} AND orderDesc like 'pro%' group by orderId, orderDesc")
Optional<List<Order>> selectionsMustBeIncludedInGroupByClause();
//Exception - Unable to retrieve enough metadata for N1QL to entity mapping, have you selected _ID and _CAS?
@Query("SELECT orderId, orderDesc FROM #{#n1ql.bucket} where #{#n1ql.filter} AND orderDesc like 'Pri%' group by orderId, orderDesc")
Optional<List<Order>> cantGroupByJustTwoPropsBecauseMissingMetadata();
//Exception - Unable to retrieve enough metadata for N1QL to entity mapping, have you selected _ID and _CAS?
@Query("SELECT distinct orderDesc FROM #{#n1ql.bucket} WHERE orderDesc like 'pro%' AND #{#n1ql.filter}")
Optional<List<String>> cantSelectDistinctBecauseMissingMetadata();
//Exception - Unable to execute query due to the following n1ql errors:
// {"msg":"Expression must be a group key or aggregate: (meta(`organism`).`id`)","code":4210}
@Query("SELECT orderId, orderDesc, META(#{#n1ql.bucket}).id as _ID, META(#{#n1ql.bucket}).cas as _CAS FROM #{#n1ql.bucket} where #{#n1ql.filter} AND orderDesc like 'Pri%' group by orderId, orderDesc")
Optional<List<Order>> cantGroupByJustTwoPropsBecauseStillMissingMetadata();
//Exception - Unable to execute query due to the following n1ql errors:
// {"msg":"Expression must be a group key or aggregate: (meta(`organism`).`id`)","code":4210}
@Query("SELECT orderId, orderDesc, META(#{#n1ql.bucket}).id as _ID, META(#{#n1ql.bucket}).cas as _CAS FROM #{#n1ql.bucket} where #{#n1ql.filter} AND orderDesc like 'Pri%' group by orderId, orderDesc, _ID, _CAS")
Optional<List<Order>> cantGroupByMETA();
@Query("SELECT distinct orderDesc, META(#{#n1ql.bucket}).id as _ID, META(#{#n1ql.bucket}).cas as _CAS FROM #{#n1ql.bucket} WHERE orderDesc like 'Pri%' AND #{#n1ql.filter}")
Optional<List<Order>> resultsAreNotDistinct();
@Query("SELECT distinct orderId, orderDesc, META(#{#n1ql.bucket}).id as _ID, META(#{#n1ql.bucket}).cas as _CAS FROM #{#n1ql.bucket} WHERE orderDesc like 'Pri%' AND #{#n1ql.filter}")
Optional<List<Order>> resultsAreNotDistinct2();
@Query("SELECT orderId, orderDesc, META(#{#n1ql.bucket}).id as _ID, META(#{#n1ql.bucket}).cas as _CAS FROM #{#n1ql.bucket} where #{#n1ql.filter} AND orderDesc like 'Pri%' group by orderId, orderDesc, META(#{#n1ql.bucket}).id, META(#{#n1ql.bucket}).cas")
Optional<List<Order>> resultsAreNotDistinct3();
到目前为止,我们能够使用此功能的唯一方法是使用com.couchbase.client.java.Bucket
from com.couchbase.client.java-client:2.7.9
。但是如果在 Spring-Data-Couchbase 中没有办法做到这一点,我会感到震惊......
String queryString = "SELECT distinct orderId, orderDesc " +
"FROM `organisms` " +
"WHERE orderDesc LIKE $search"
Cluster cluster = CouchbaseCluster.create(hosts).authenticate(bucketUser, bucketPassword);
Bucket bucket = cluster.openBucket(bucketName);
JsonObject parameters = JsonObject.create().put("search", search);
ParameterizedN1qlQuery q = N1qlQuery.parameterized(queryString, parameters);
N1qlQueryResult result = bucket.query(q);
解决方案
Couchbase Spring Data 的当前实现将始终尝试获取 CAS 和文档 ID(因为元数据对于某些用例很重要),这就是您收到此错误的原因。
如果您只需要返回所需的属性,我建议使用类似于以下的语法:
@Override
public List<KeyValueVO> listKeyValue(String companyId) {
String bucketName = securityGroupRepository.getCouchbaseOperations().getCouchbaseBucket().name();
String queryString = "SELECT meta().id as id, name FROM "+bucketName+
" WHERE _class = '"+SecurityGroup.class.getName() +"' and removed = false and companyId = '"+companyId+"' order by name ";
N1qlParams params = N1qlParams.build().consistency(ScanConsistency.NOT_BOUNDED).adhoc(true);
ParameterizedN1qlQuery query = N1qlQuery.parameterized(queryString, JsonObject.create(), params);
return securityGroupRepository.getCouchbaseOperations().findByN1QLProjection(query, KeyValueVO.class);
}
PS:是的,我应该正确使用 ParameterizedN1qlQuery 而不是 concat 字符串中的参数,但你明白了。
推荐阅读
- ios - 如何在 React Native 中访问 iOS 用户默认值?
- angular - 如果 rxjs observable 不对变化做出反应,它的目的是什么?
- android - Gradle task to automate release notes from commit message in gradle for Firebase App Distribution dev builds
- elasticsearch - calculate if the timestamp is greater or smaller than current time
- java - Substracting MINUTE from Calender not working correctly
- unity3d - Is there a fast way to detect drag events on a game object in Unity?
- python - to_excel() 没有索引布局
- angular - 动态表的页脚值 - Angular
- javascript - 节点 JS 部署
- node.js - Node.js:将 Cucumber JSON 结果发送到 Jira 的 Xray - jira-client-xray 给出 HTTP 405 错误