0

I'm writing couchbase repository using Spring module and I'm trying to add my own implementation of count method using N1QL query:

public interface MyRepository extends CouchbaseRepository<Entity, Long> {
    @Query("SELECT count(*) FROM default")
    long myCount();
}

But it doesn't work:

org.springframework.data.couchbase.core.CouchbaseQueryExecutionException: Unable to retrieve enough metadata for N1QL to entity mapping, have you selected _ID and _CAS?

So my question is: how can I write counting query using spring-data-couchbase?

I cannot find anything about this in spring documentation. link

IgorekPotworek
  • 1,317
  • 13
  • 33

3 Answers3

4

This exception happens because the @Query annotation was designed with the use-case of retrieving entities in mind. Projections to a scalar like count are uncovered corner cases as of RC1. Maybe I can think of some way of adding support for it through explicit boolean flag in the annotation?

Unfortunately I was unable to find a workaround. I was trying to come up with a custom repository method implementation but it appears support for it is broken in 2.0.0-RC1 :(

edit: The use case of simple return types like long, with a SELECT that only uses a single aggregation, should work so this is a bug/improvement. I've opened ticket DATACOUCH-187 in the Spring Data JIRA.

Simon Baslé
  • 27,105
  • 5
  • 69
  • 70
  • Thanks for yours reply. So only primitive result types are unsupported? Something like this: @Query("SELECT count(*) FROM default") CountDTO myCount(); should work? – IgorekPotworek Jan 11 '16 at 17:47
  • I try this and it doesn't work too. So, is there any solution to create repository method (N1QL) that return something different than repository entity(in my example something different than Entity class)? – IgorekPotworek Jan 11 '16 at 17:54
  • no `@Query(string)` are basically limited to entity classes. what N1QL returns when performing a `SELECT count(*)` is not even easy to work with in terms of transcoding since the JSON resembles something like `{ "$1": 100 }`. So you have to give it an alias and transcode to an adhoc class like `CountFragment`: `SELECT count(*) AS count` => `{ "count": 100}` => `class CountFragment { public long count; }`. – Simon Baslé Jan 11 '16 at 18:13
  • 2
    in the `CouchbaseTemplate` there is a `findByN1qlProjection` method that could be used to obtain a collection of count fragments: `long count = findByN1qlProjection(N1qlQuery.simple("SELECT count(*) AS " + CountFragment.COUNT_ALIAS + " FROM default"), CountFragment.class).get(0).count();` – Simon Baslé Jan 11 '16 at 18:17
  • Is it only solution for querying something different than entity classes? – IgorekPotworek Jan 12 '16 at 10:03
  • yeah but I'll open a ticket and implement support for simple return types like long (at least provided that the select clause uses a single aggregation function like count) – Simon Baslé Jan 12 '16 at 12:04
  • Ok. Great. But what with cases like i.e. : N1qlQuery q = N1qlQuery.simple("SELECT count(*) as num, city FROM default group by city"); CityCount a = couchbaseTemplate.findByN1QLProjection(q, CityCount.class).get(0); I mean when I want to query to entity different than repository entity? – IgorekPotworek Jan 12 '16 at 13:07
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/100483/discussion-between-simon-basle-and-igorekpotworek). – Simon Baslé Jan 12 '16 at 13:51
1
@Query("SELECT count(*) , META(default).id as _ID, META(default).cas as _CAS FROM default")

Change your query to this one.

Can Bezmen
  • 112
  • 6
0

Use this query : 

 @Query("SELECT count(*) as count FROM #{#n1ql.bucket} WHERE #{#n1ql.filter} ")
 long myCount();
Sajit Gupta
  • 98
  • 1
  • 6