Not able to get pagination result with Couchbase Paging And Sorting Repository

Java SDK
1

Hi,
I have a query related to CouchbasePagingAndSortingRepository for Spring boot. I am using spring-boot-starter-data-couchbase.

The following is the query and the code that I am executing,

@Query(“SELECT META(parent).id AS _ID, META(parent).cas AS _CAS, parent.*, weeklyData FROM #{n1ql.bucket} parent NEST #{n1ql.bucket} weeklyData ON parent.typeId = weeklyData.belongsTo AND weeklyData.weekStartDate Between $3 AND $4 WHERE parent.id = $1 AND parent.type = $2”)
Page findDataById(Integer id, String type, String startDate, String endDate, Pageable pageable);

The same query gives results if I replace Page by List and add LIMIT and OFFSET like this:

@Query(“SELECT META(parent).id AS _ID, META(parent).cas AS _CAS, parent.*, weeklyData FROM #{n1ql.bucket} parent NEST #{n1ql.bucket} weeklyData ON parent.typeId = weeklyData.belongsTo AND weeklyData.weekStartDate Between $3 AND $4 WHERE parent.id = $1 AND parent.type = $2 LIMIT $6 OFFSET $5”)
List findDataById(Integer id, String type, String startDate, String endDate, Integer pageNumber, Integer pageSize);

Using CouchbasePagingAndSortingRepository for pagination results, I get the following error:

com.fasterxml.jackson.databind.exc.UnrecognizedPropertyException: Unrecognized field “weeklyData” (class org.springframework.data.couchbase.repository.query.CountFragment), not marked as ignorable (one known property: “count”])
at [Source: (String)"{“weeklyData”:[truncated 524 chars]}; line: 1, column: 16] (through reference chain: org.springframework.data.couchbase.repository.query.CountFragment[“weeklyData”])
at com.fasterxml.jackson.databind.exc.UnrecognizedPropertyException.from(UnrecognizedPropertyException.java:61) ~[jackson-databind-2.10.0.jar:2.10.0]
at com.fasterxml.jackson.databind.DeserializationContext.handleUnknownProperty(DeserializationContext.java:840) ~[jackson-databind-2.10.0.jar:2.10.0]
at com.fasterxml.jackson.databind.deser.std.StdDeserializer.handleUnknownProperty(StdDeserializer.java:1179) ~[jackson-databind-2.10.0.jar:2.10.0]
at com.fasterxml.jackson.databind.deser.BeanDeserializerBase.handleUnknownProperty(BeanDeserializerBase.java:1592) ~[jackson-databind-2.10.0.jar:2.10.0]
at com.fasterxml.jackson.databind.deser.BeanDeserializerBase.handleUnknownVanilla(BeanDeserializerBase.java:1570) ~[jackson-databind-2.10.0.jar:2.10.0]
at com.fasterxml.jackson.databind.deser.BeanDeserializer.vanillaDeserialize(BeanDeserializer.java:294) ~[jackson-databind-2.10.0.jar:2.10.0]
at com.fasterxml.jackson.databind.deser.BeanDeserializer.deserialize(BeanDeserializer.java:151) ~[jackson-databind-2.10.0.jar:2.10.0]
at com.fasterxml.jackson.databind.ObjectMapper._readMapAndClose(ObjectMapper.java:4202) ~[jackson-databind-2.10.0.jar:2.10.0]
at com.fasterxml.jackson.databind.ObjectMapper.readValue(ObjectMapper.java:3205) ~[jackson-databind-2.10.0.jar:2.10.0]
at com.fasterxml.jackson.databind.ObjectMapper.readValue(ObjectMapper.java:3173) ~[jackson-databind-2.10.0.jar:2.10.0]
at org.springframework.data.couchbase.core.convert.translation.JacksonTranslationService.decodeFragment(JacksonTranslationService.java:243) ~[spring-data-couchbase-3.2.1.RELEASE.jar:3.2.1.RELEASE]
at org.springframework.data.couchbase.core.CouchbaseTemplate.findByN1QLProjection(CouchbaseTemplate.java:489) ~[spring-data-couchbase-3.2.1.RELEASE.jar:3.2.1.RELEASE]
at org.springframework.data.couchbase.repository.query.AbstractN1qlBasedQuery.executePaged(AbstractN1qlBasedQuery.java:182) ~[spring-data-couchbase-3.2.1.RELEASE.jar:3.2.1.RELEASE]
at org.springframework.data.couchbase.repository.query.AbstractN1qlBasedQuery.executeDependingOnType(AbstractN1qlBasedQuery.java:133) ~[spring-data-couchbase-3.2.1.RELEASE.jar:3.2.1.RELEASE]
at org.springframework.data.couchbase.repository.query.AbstractN1qlBasedQuery.execute(AbstractN1qlBasedQuery.java:112) ~[spring-data-couchbase-3.2.1.RELEASE.jar:3.2.1.RELEASE]
at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:618) ~[spring-data-commons-2.2.1.RELEASE.jar:2.2.1.RELEASE]
at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:605) ~[spring-data-commons-2.2.1.RELEASE.jar:2.2.1.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.1.RELEASE.jar:5.2.1.RELEASE]
at org.springframework.data.couchbase.repository.support.ViewPostProcessor$ViewInterceptor.invoke(ViewPostProcessor.java:87) ~[spring-data-couchbase-3.2.1.RELEASE.jar:3.2.1.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.1.RELEASE.jar:5.2.1.RELEASE]
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:93) ~[spring-aop-5.2.1.RELEASE.jar:5.2.1.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.1.RELEASE.jar:5.2.1.RELEASE]
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:93) ~[spring-aop-5.2.1.RELEASE.jar:5.2.1.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.1.RELEASE.jar:5.2.1.RELEASE]
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) ~[spring-aop-5.2.1.RELEASE.jar:5.2.1.RELEASE]
at com.sun.proxy.$Proxy99. findDataById(Unknown Source) ~[na:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:na]
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:566) ~[na:na]
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344) ~[spring-aop-5.2.1.RELEASE.jar:5.2.1.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198) ~[spring-aop-5.2.1.RELEASE.jar:5.2.1.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.2.1.RELEASE.jar:5.2.1.RELEASE]
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139) ~[spring-tx-5.2.1.RELEASE.jar:5.2.1.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.1.RELEASE.jar:5.2.1.RELEASE]
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) ~[spring-aop-5.2.1.RELEASE.jar:5.2.1.RELEASE]
at com.sun.proxy.$Proxy99. findDataById(Unknown Source) ~[na:na]

Wanted to know what I am doing wrong here?

2

To compute the ‘total’ that is returned in the Page object, the count() of the query is executed. To construct the count query, the n1ql.selectEntity expression is replaced with corresponding "SELECT COUNT(*) AS … " therefore the source @Query must use the n1ql.selectEntity and begin with …

@Query("#{#n1ql.selectEntity} where

so your @Query should look like

@Query(“#{#n1ql.selectEntity} FROM #{#n1ql.bucket} parent NEST #{#n1ql.bucket} weeklyData ON parent.typeId = weeklyData.belongsTo AND weeklyData.weekStartDate Between $3 AND $4 WHERE parent.id = $1 AND parent.type = $2”)

I haven’t worked through the rest of this, that change may or may not be sufficient - the n1ql.selectEntity for the actual query (not the COUNT(*)), is constructed as below. It seems that will not give the same result as your query.

		String b = "`" + bucketName + "`";
	String entity = "META(" + b + ").id AS " + SELECT_ID + ", META(" + b + ").cas AS " + SELECT_CAS;
	String count = "COUNT(*) AS " + CountFragment.COUNT_ALIAS;
	String selectEntity;
	if (isCount) {
		selectEntity = "SELECT " + count + " FROM " + b;
	} else {
		selectEntity = "SELECT " + entity + ", " + b + ".* FROM " + b;
	}

In my opinion, it’s better to have the client implement the paging by controlling offset and limit like you have in your second query. It eliminates the need for the COUNT(*) query, and can also be used on a Reactive repository.

Make sure you are using spring-data-couchbase 4.0.2 for @Query with LIMIT and OFFSET.

btw - I think your spel expression for bucket is missing a ‘#’ after the first brace -> #{#n1ql.bucket}

1 Like
3

Thanks mreiche,

This helped me a lot.

#{#n1ql.selectEntity} worked and was able to get the pagination results.

I wanted to get the contents as well as the total count of the documents in a single query.

My use case is as below:
I have two document types and I want to combine the two document sets together in a single query and for that I was using
parent.*, weeklyData in the SELECT query

SELECT META( parent ).id AS _ID, META( parent ).cas AS _CAS, parent.*, weeklyData FROM #{n1ql.bucket} parent

I tried the following to get the desired result:

SELECT #{#n1ql.fields}, parent.* ,weeklyData FROM #{#n1ql.bucket}

But this also failed, because of the same reason you mentioned in the above comment.

These are the examples of the two document sets I have:

{
     "parent":{
         "docValue":"somevalue",
         "id":12345,
         "type":"sometype",
         "typeId":"12345"
     }
}

{
        "belongsTo": ":12345",
       // some attributes here
        "weekEndDate": "07/01/2019",
        "weekStartDate": "01/01/2019"
  }

Here, the two documents are linked by 1-many relationship through parent.typeId = weeklyData.belongsTo
And I want to display the result like this:

[
  {
    "parent": {
      "docValue": "somevalue",
      "id": 12345,
      "type": "sometype",
      "typeId": "12345"
    },
    "weeklyData": [
      {
        "belongsTo": "12345", 
        // some attributes here
        "weekEndDate": "07/01/2019",
        "weekStartDate": "01/01/2019"
      },
      {
        "belongsTo": "12345",
        // some attributes here
        "weekEndDate": "08/01/2019",
        "weekStartDate": "14/01/2019"
      }
    ]
  }
]

Wanted to check if there is any way to do the same(combine the two document sets) in a single query, or if you suggest a better way of doing it than what I am currently doing through the queries above. I can try using the way you suggested if nothing else is possible.

Thanks in advance.

4

I suggest doing paging yourself, by managing limit and skip in your application. Use List as the return type instead of Page

key-set pagination ( https://use-the-index-luke.com/no-offset ) is a much more efficient way of paging - but you’ll need to implement that in your application. It uses the indexes to get the items beginning with the first one required, instead of “skipping” over the ones in prior pages.

1 Like
5
1 Like