Spring Boot Count Query

Java SDK
1

I’m trying to pull a count from couchbase using spring boot’s Couchbase repo. When I run this query in the couchbase console It works as intended (pulls numbers of attempts within 24 hours of the most recent attempt where the most recent must be within 24 hours of now.)

I dont think the query is the issue, but I think the issue is spring boot doesn’t like executing count queries the same way it pulls entire objects.

public interface AttemptRepository extends CouchbaseRepository<Entity, Long>

{

@Query(
"Select count(*) attempts from {n1ql.bucket} outer where "

  • "outer.id = "$1 "
  • "and DATE_ADD_STR(outer.createdDate,24,‘hour’) >= "
  • "(select RAW MAX(createdDate) lastAttempt "
  • "from {n1ql.bucket} inner where "
  • "inner.id = "$1 "
  • “and inner.createdDate > DATE_ADD_STR(NOW_STR(),-24,‘hour’))[0]”
    )
    Long countAttempts(String id);
    }

similar post:

couldn’t get this to work
META(default).id as _ID, META(default).cas as _CAS FROM default

2

Hi @lukel - are there two separate questions? One about “count” and one about "META(default).id? Are you getting an error? If so, what error? Can you show the complete definition of your @Query method? (especially the method signature). What version of spring-data-couchbase are you using?

There is a count query here - notice that the query name begins with ‘count’ and the return type is Long.

    @Query("SELECT count(*) FROM #{#n1ql.bucket}")
    Long countGood();
3

Hi mreiche, thanks for the reply

I’m only asking about the count query - I only included that META stuff because I saw it as a suggestion in another post, so feel free to ignore it if it doesn’t pertain.

I’m using

using spring-couchbase-5.2.0

That is the whole method signature (it’s actually the entire class minus the imports). Let me copy it straight over (I cannot screenshot it)

public interface AttemptRepository extends CouchbaseRepository<Entity, Long>
{

@Query(
"Select count(*) attempts from {n1ql.bucket} outer where " +
"outer.id = "$1 " +
"and DATE_ADD_STR(outer.createdDate,24,‘hour’) >= " +
"(select RAW MAX(createdDate) lastAttempt " +
"from {n1ql.bucket} inner where " +
"inner.id = "$1 " +
“and inner.createdDate > DATE_ADD_STR(NOW_STR(),-24,‘hour’))[0]”
)
Long countAttempts(String id);

}

When this runs I get a response from the method with value=0 (when I run it in the couchbase console it gives me the proper value). I’ve gotten some errors with couchbase parser and wrong types when tinkering around with it, but couchbase is returning a long equal to zero when I run as is.

4

That is the whole method signature

My mistake - bad reading.

inner and outer are reserved words, so that query won’t run in the console nor in spring data couchbase. Also - there is an unmatched quote before both occurences of the $1. No quote is needed there

Can you show the query you are running in the console (are you using it as shown with $1 (if so, what are you using for the parameter), or replacing it with a literal) and the json result?
And can you show the code that calls countAttempts()?
Please post as ‘pre-formatted’ so the quotes etc. don’t get mangled in the html.

I think the issue is spring boot doesn’t like executing count queries the same way it pulls entire objects

You can just use select count(*) from #{#n1ql.bucket} to test that.

6

code

7

Remove the quotes from around $partyId in both places. When you have the quotes, it will match documents with o1.status = ‘REFUSED’ and o1.partyId = ‘$partyId’. You probably want it to match o1.partyId = ‘12345’ or something like that.

Also - in the query editor query, you show two different values for o.partyId and o1.partyId (maybe that’s a typo). If you want to do the same in your spring data query, you’ll need two parameters.

8

Looks like it was the quotes. Thank you!

1 Like
9

Great. I should have thought of this earlier - but if you enable debug logging for org.springframework.data.couchbase, the query statement sent to couchbase will be logged.

logback.xml

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
    <appender name="console" class="ch.qos.logback.core.ConsoleAppender">
        <encoder>
            <pattern>%d %5p %40.40c:%4L - %m%n</pattern>
        </encoder>
    </appender>
    <root level="info">
        <appender-ref ref="console"/>
    </root>
    <logger name="org.springframework.data.couchbase.core" level="debug"/>"
</configuration>
10

The following might give better performance

WITH updated AS ((SELECT RAW MAX(o1.updatedOn)
                    FROM bucket AS o1
                    WHERE o1.partyId = $partyId
                          AND o1.status = 'REFUSED'
                          AND o1.error.code = '5004'
                          AND o1.createdOn > DATE_ADD_STR(NOW_STR(),-24,'hour'))[0])
SELECT COUNT(1)
FROM bucket AS o
WHERE o.partyId = $partyId
      AND o.status = 'REFUSED'
      AND o.error.code = '5004'
      AND o.createdOn >= DATE_ADD_STR(updated,-24,'hour');
  • Define subquery AS cte so that value statically available for main query and push the value to indexer as part of the scan. See Subquery section IN List Handling Improvements in Couchbase Server 6.5 - The Couchbase Blog
  • main query. createdOn + x >= static . As there is no index on CreatedOn +x it can’t push . If you rewrite as createdOn >= static - x it can push index if field is CreatedOn
1 Like
11

will give this a shot - much more readable too!

12

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.